Reverse the "natural order" of a MySQL table without ORDER BY ?

Question

I'm dealing with a legacy database table that has no insertion date column or unique id column, however the natural order of insertion is still valid when examined with a simple SELECT * showing oldest to newest.

I'd like like to fetch that data with pagination but reverse the order as if it was ORDER BY date DESC

I've thought about wrapping the query, assigning a numeric id to the resulting rows and then do an ORDER BY on the result but wow that seems crazy.

Is there a more simple solution I am overlooking?

I cannot add columns to the existing table, I have to work with it as is.

Thanks for any ideas!

Answer 1

If you're writing an application to process the data, another approach might be to run your current query, then iterate over the returned records from last to first.

If you have too many records, then you may wish to instead use a view. This is a Database object which can be used to combine data from different tabls, or present a modified view of a single table, amongst other things. In this case, you could try creating a view of your table and add a generated ID column. You could then run SELECT statements against this view ordering by the new column you have added.

However be aware of the advice from another poster above: the order in which rows are returned without an ORDER BY clause is arbitrary and may change without notification. It would be best to amend your table if at all possible.

mySQL CREATE VIEW syntax

Answer 2

Simply use @rownum in your query to number each row and then order by the @rownum desc. Here's an example.

select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10;

Finally, beware that relying on the current order being returned long-term isn't recommended.