CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 3095

MySQL select last 'n' records by date, but sorted oldest to newest

I have a table that has transactions with a datetime column. I'm trying to select the last 'n' records (i.e. 20 rows) but have it sorted oldest to newest.

SELECT *
FROM table
WHERE 1=1
ORDER BY table.datefield DESC
LIMIT 20;

Gives me the 20 most recent, but in the opposite order.

Is this possible in one query, or will I have to do a query to get total rows and then adjust the limit based on that so I can do the table.datefiled ASC and then limit (total rows - n), n

Upvotes: 2

Views: 3107

Answers (1)

VMai
VMai

Reputation: 10336

Building a SELECT around your original SELECT and convert this to a derived table should do it

SELECT t.*
FROM (
    SELECT *
    FROM table
    WHERE 1=1
    ORDER BY table.datefield DESC
    LIMIT 20
) t
ORDER BY t.datefield

Upvotes: 6

Related Questions