Search code examples
mysqlsql-order-by

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

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!

Solution

  • 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.