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?
-
2Duplicate: stackoverflow.com/questions/1793147/…OMG Ponies– OMG Ponies2009-11-27 06:34:21 +00:00Commented Nov 27, 2009 at 6:34
-
1There is no such thing as a "default ordering" so you cannot "reverse" it.user330315– user3303152014-03-17 17:32:05 +00:00Commented Mar 17, 2014 at 17:32
-
This question is similar to: SQL best practice to deal with default sort order. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem.philipxy– philipxy2024-07-26 04:26:47 +00:00Commented Jul 26, 2024 at 4:26
Add a comment
|
5 Answers
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.
1 Comment
philipxy
@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.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.
2 Comments
Steven
The order I need is the reverse order in which the rows were inserted. "First inserted, first out".
OMG Ponies
@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.There is no guaranteed order if you don't specify an ORDER BY clause, thus the 'reverse of the default order' is undefined.
1 Comment
Patrick
For those who prefer answers with evidence to back up the claims made: stackoverflow.com/questions/8746519/…
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"
4 Comments
Steven
But what a pity, there is no "Id" field in the table.
pavium
But if there is no Id field, or equivalent, is the order meaningful?
Steven
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".
micahwittman
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.
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.
