Python order by desc

Sorting HOW TO¶

Python lists have a built-in list.sort() method that modifies the list in-place. There is also a sorted() built-in function that builds a new sorted list from an iterable.

In this document, we explore the various techniques for sorting data using Python.

Sorting Basics¶

A simple ascending sort is very easy: just call the sorted() function. It returns a new sorted list:

>>> sorted([5, 2, 3, 1, 4]) [1, 2, 3, 4, 5] 

You can also use the list.sort() method. It modifies the list in-place (and returns None to avoid confusion). Usually it’s less convenient than sorted() — but if you don’t need the original list, it’s slightly more efficient.

>>> a = [5, 2, 3, 1, 4] >>> a.sort() >>> a [1, 2, 3, 4, 5] 

Another difference is that the list.sort() method is only defined for lists. In contrast, the sorted() function accepts any iterable.

>>> sorted(1: 'D', 2: 'B', 3: 'B', 4: 'E', 5: 'A'>) [1, 2, 3, 4, 5] 

Key Functions¶

Both list.sort() and sorted() have a key parameter to specify a function (or other callable) to be called on each list element prior to making comparisons.

For example, here’s a case-insensitive string comparison:

>>> sorted("This is a test string from Andrew".split(), key=str.lower) ['a', 'Andrew', 'from', 'is', 'string', 'test', 'This'] 

The value of the key parameter should be a function (or other callable) that takes a single argument and returns a key to use for sorting purposes. This technique is fast because the key function is called exactly once for each input record.

A common pattern is to sort complex objects using some of the object’s indices as keys. For example:

>>> student_tuples = [ . ('john', 'A', 15), . ('jane', 'B', 12), . ('dave', 'B', 10), . ] >>> sorted(student_tuples, key=lambda student: student[2]) # sort by age [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] 

The same technique works for objects with named attributes. For example:

>>> class Student: . def __init__(self, name, grade, age): . self.name = name . self.grade = grade . self.age = age . def __repr__(self): . return repr((self.name, self.grade, self.age)) >>> student_objects = [ . Student('john', 'A', 15), . Student('jane', 'B', 12), . Student('dave', 'B', 10), . ] >>> sorted(student_objects, key=lambda student: student.age) # sort by age [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] 

Operator Module Functions¶

The key-function patterns shown above are very common, so Python provides convenience functions to make accessor functions easier and faster. The operator module has itemgetter() , attrgetter() , and a methodcaller() function.

Using those functions, the above examples become simpler and faster:

>>> from operator import itemgetter, attrgetter >>> sorted(student_tuples, key=itemgetter(2)) [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] >>> sorted(student_objects, key=attrgetter('age')) [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] 

The operator module functions allow multiple levels of sorting. For example, to sort by grade then by age:

>>> sorted(student_tuples, key=itemgetter(1,2)) [('john', 'A', 15), ('dave', 'B', 10), ('jane', 'B', 12)] >>> sorted(student_objects, key=attrgetter('grade', 'age')) [('john', 'A', 15), ('dave', 'B', 10), ('jane', 'B', 12)] 

Ascending and Descending¶

Both list.sort() and sorted() accept a reverse parameter with a boolean value. This is used to flag descending sorts. For example, to get the student data in reverse age order:

>>> sorted(student_tuples, key=itemgetter(2), reverse=True) [('john', 'A', 15), ('jane', 'B', 12), ('dave', 'B', 10)] >>> sorted(student_objects, key=attrgetter('age'), reverse=True) [('john', 'A', 15), ('jane', 'B', 12), ('dave', 'B', 10)] 

Sort Stability and Complex Sorts¶

Sorts are guaranteed to be stable. That means that when multiple records have the same key, their original order is preserved.

>>> data = [('red', 1), ('blue', 1), ('red', 2), ('blue', 2)] >>> sorted(data, key=itemgetter(0)) [('blue', 1), ('blue', 2), ('red', 1), ('red', 2)] 

Notice how the two records for blue retain their original order so that (‘blue’, 1) is guaranteed to precede (‘blue’, 2) .

This wonderful property lets you build complex sorts in a series of sorting steps. For example, to sort the student data by descending grade and then ascending age, do the age sort first and then sort again using grade:

>>> s = sorted(student_objects, key=attrgetter('age')) # sort on secondary key >>> sorted(s, key=attrgetter('grade'), reverse=True) # now sort on primary key, descending [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] 

This can be abstracted out into a wrapper function that can take a list and tuples of field and order to sort them on multiple passes.

>>> def multisort(xs, specs): . for key, reverse in reversed(specs): . xs.sort(key=attrgetter(key), reverse=reverse) . return xs >>> multisort(list(student_objects), (('grade', True), ('age', False))) [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] 

The Timsort algorithm used in Python does multiple sorts efficiently because it can take advantage of any ordering already present in a dataset.

Decorate-Sort-Undecorate¶

This idiom is called Decorate-Sort-Undecorate after its three steps:

• First, the initial list is decorated with new values that control the sort order.
• Second, the decorated list is sorted.
• Finally, the decorations are removed, creating a list that contains only the initial values in the new order.

For example, to sort the student data by grade using the DSU approach:

>>> decorated = [(student.grade, i, student) for i, student in enumerate(student_objects)] >>> decorated.sort() >>> [student for grade, i, student in decorated] # undecorate [('john', 'A', 15), ('jane', 'B', 12), ('dave', 'B', 10)] 

This idiom works because tuples are compared lexicographically; the first items are compared; if they are the same then the second items are compared, and so on.

It is not strictly necessary in all cases to include the index i in the decorated list, but including it gives two benefits:

• The sort is stable – if two items have the same key, their order will be preserved in the sorted list.
• The original items do not have to be comparable because the ordering of the decorated tuples will be determined by at most the first two items. So for example the original list could contain complex numbers which cannot be sorted directly.

Another name for this idiom is Schwartzian transform, after Randal L. Schwartz, who popularized it among Perl programmers.

Now that Python sorting provides key-functions, this technique is not often needed.

Comparison Functions¶

Unlike key functions that return an absolute value for sorting, a comparison function computes the relative ordering for two inputs.

For example, a balance scale compares two samples giving a relative ordering: lighter, equal, or heavier. Likewise, a comparison function such as cmp(a, b) will return a negative value for less-than, zero if the inputs are equal, or a positive value for greater-than.

It is common to encounter comparison functions when translating algorithms from other languages. Also, some libraries provide comparison functions as part of their API. For example, locale.strcoll() is a comparison function.

To accommodate those situations, Python provides functools.cmp_to_key to wrap the comparison function to make it usable as a key function:

sorted(words, key=cmp_to_key(strcoll)) # locale-aware sort order 

Odds and Ends¶

• For locale aware sorting, use locale.strxfrm() for a key function or locale.strcoll() for a comparison function. This is necessary because “alphabetical” sort orderings can vary across cultures even if the underlying alphabet is the same.
• The reverse parameter still maintains sort stability (so that records with equal keys retain the original order). Interestingly, that effect can be simulated without the parameter by using the builtin reversed() function twice:

>>> data = [('red', 1), ('blue', 1), ('red', 2), ('blue', 2)] >>> standard_way = sorted(data, key=itemgetter(0), reverse=True) >>> double_reversed = list(reversed(sorted(reversed(data), key=itemgetter(0)))) >>> assert standard_way == double_reversed >>> standard_way [('red', 1), ('red', 2), ('blue', 1), ('blue', 2)] 

>>> Student.__lt__ = lambda self, other: self.age  other.age >>> sorted(student_objects) [('dave', 'B', 10), ('jane', 'B', 12), ('john', 'A', 15)] 

>>> students = ['dave', 'john', 'jane'] >>> newgrades = 'john': 'F', 'jane':'A', 'dave': 'C'> >>> sorted(students, key=newgrades.__getitem__) ['jane', 'dave', 'john'] 

Источник

Python MySQL Order By

Use the ORDER BY statement to sort the result in ascending or descending order.

The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.

Example

Sort the result alphabetically by name: result:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

sql = «SELECT * FROM customers ORDER BY name»

ORDER BY DESC

Use the DESC keyword to sort the result in a descending order.

Example

Sort the result reverse alphabetically by name:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

sql = «SELECT * FROM customers ORDER BY name DESC»

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

Thank You For Helping Us!

Your message has been sent to W3Schools.

Top Tutorials

Top References

Top Examples

Get Certified

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

Источник

SQLAlchemy ORDER BY DESCENDING?

How can I use ORDER BY descending in a SQLAlchemy query like the following? This query works, but returns them in ascending order:

query = (model.Session.query(model.Entry) .join(model.ClassificationItem) .join(model.EnumerationValue) .filter_by(id=c.row.id) .order_by(model.Entry.amount) # This row :) ) 

.order_by(desc(model.Entry.amount)) 

6 Answers 6

Just as an FYI, you can also specify those things as column attributes. For instance, I might have done:

.order_by(model.Entry.amount.desc()) 

This is handy since it avoids an import , and you can use it on other places such as in a relation definition, etc.

For more information, you can refer this SQLAlchemy 1.4 Documentation

The sqlalchemy orm doc is a mystery for me, i can never find anything by myself in it. Thank you @dzervas

from sqlalchemy import desc someselect.order_by(desc(table1.mycol)) 

One other thing you might do is:

from sqlalchemy.sql import text . .order_by(text("name desc") 

This will result in: ORDER BY name desc. The disadvantage here is the explicit column name used in order by.

This is nice if you have the sort column in a string for any reason, though (like sanitized input in a web API).

@JimStewart and for passing in direction I ended up using: qry.order_by(getattr(getattr(Song, sort_by), sort_dir)())

You can also use this if you have labeled your output and are sorting by a computed column instead of a model column

You can use .desc() function in your query just like this

query = (model.Session.query(model.Entry) .join(model.ClassificationItem) .join(model.EnumerationValue) .filter_by(id=c.row.id) .order_by(model.Entry.amount.desc()) ) 

This will order by amount in descending order or

query = session.query( model.Entry ).join( model.ClassificationItem ).join( model.EnumerationValue ).filter_by( model.Entry.amount.desc() ) ) 

Use of desc function of SQLAlchemy

from sqlalchemy import desc query = session.query( model.Entry ).join( model.ClassificationItem ).join( model.EnumerationValue ).filter_by( desc(model.Entry.amount) ) ) 

For official docs please use the link or check below snippet

sqlalchemy.sql.expression.desc(column) Produce a descending ORDER BY clause element.

e.g.:

from sqlalchemy import desc stmt = select([users_table]).order_by(desc(users_table.c.name)) 

SELECT id, name FROM user ORDER BY name DESC 

The desc() function is a standalone version of the ColumnElement.desc() method available on all SQL expressions, e.g.:

stmt = select([users_table]).order_by(users_table.c.name.desc()) 

Parameters column – A ColumnElement (e.g. scalar SQL expression) with which to apply the desc() operation.

See also

asc()

nullsfirst()

nullslast()

Select.order_by()

Источник