In MySQL, when you execute a select SQL statement, there is a default ordering if you don't include a sorting clause. How can I reverse the default ordering? Just add DESC?
Asked
Active
Viewed 9,933 times
9
Peter Mortensen
- 30,738
- 21
- 105
- 131
Steven
- 24,410
- 42
- 108
- 130
-
2Duplicate: http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order/1793162#1793162 – OMG Ponies Nov 27 '09 at 06:34
-
1There is no such thing as a "default ordering" so you cannot "reverse" it. – Mar 17 '14 at 17:32
5 Answers
7
You can set a counter in your result fields and sort using it:
SELECT *, @counter := @counter + 1 AS 'counter' FROM tableName, (SELECT @counter := 0) r ORDER BY counter DESC
I think it will work as you want.
Mohsen Haeri
- 337
- 4
- 8
-
1
-
@Jerry But this is just the reverse of whatever order the rows were retrieved in, which doesn't have any guaranteed connection to the implementation order, which in turn has no guarantee to be anything in particular. If you want an order guaranteed, it has to be in the data. *Also* there are no guarantees for queries that both set & read a variable, so this query has undefined behaviour. For *a certain recent build* of 5.7 people at Percona who looked at the implementation saw that certain use of variables in `case` expressions (*not* like this) gives certain predictable behaviour. Until. – philipxy Oct 21 '17 at 08:56
5
If you want the data to come out consistently ordered, you have to use ORDER BY followed by the column(s) you want to order the query by. ASC is the default, so you don't need to specify it. IE:
ORDER BY your_column
...is the equivalent to:
ORDER BY your_column ASC
ASC/DESC is on a per column basis. For example:
ORDER BY first_column, second_column DESC
...means that the query will sort the resultset as a combination using the first_column in ascending order, second_column in descending order.
OMG Ponies
- 325,700
- 82
- 523
- 502
-
The order I need is the reverse order in which the rows were inserted. "First inserted, first out". – Steven Nov 27 '09 at 06:42
-
1@Steven: You'll have to provide the output of `DESCRIBE [your table name here]` from your database before I can suggest what to use. If you have an autonumber primary key column - order by it `ASC`. Next best thing would be a date_created column, using the datetime datatype. Again, `ASC`. – OMG Ponies Nov 27 '09 at 06:51
3
There is no guaranteed order if you don't specify an ORDER BY clause, thus the 'reverse of the default order' is undefined.
Amber
- 507,862
- 82
- 626
- 550
-
For those who prefer answers with evidence to back up the claims made: https://stackoverflow.com/questions/8746519/sql-what-is-the-default-order-by-of-queries – Patrick Jun 26 '17 at 00:47
1
I think you would be better served by specifying the order you actually want. Tables, by their nature, have no order. It is probably just displayed in the order in which the rows were inserted - though there's no guarantee it will stay in that order.
Chances are, you probably just want to add this:
ORDER BY id DESC
...since most of the time, people use an auto-incrementing field called "id"
nickf
- 537,072
- 198
- 649
- 721
-
-
-
I think the default order in which the rows were inserted, the default order is "First inserted, last out". I want to change it to "First inserted, first out". – Steven Nov 27 '09 at 06:40
-
If there is no logical timestamp nor some kind of increment field, then you have insufficient information available to determine the historic order of inserts. – micahwittman Nov 27 '09 at 06:57
1
Unless you can specify a column name in an ORDER BY clause, you can't use DESC, and you'll have to resort to tricks involving LIMIT to see the last few records.
This would be unsatisfactory, I think.
pavium
- 14,808
- 4
- 33
- 50