ck_ ck_ - 6 months ago 32
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

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

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.

Comments