ck_ ck_ - 1 year ago 91
MySQL Question

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

showing oldest to newest.

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

I've thought about wrapping the query, assigning a numeric id to the resulting rows and then do an
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 Source

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.