[SOLVED] Limit to last X entries in a query instead of first X?

[pnj]

I want to order a list by date, but limit it to the 3 most recent dates in the list.  Just the limit part would look like this:

[code]
SELECT x, xdate FROM table ORDER BY xdate DESC LIMIT 3;
[/code]

But then the entries are sorted with the most recent first.

The problem can be solved with a subquery:

[code]SELECT x, xdate from (SELECT x, xdate FROM table ORDER BY xdate DESC LIMIT 3) AS y ORDER BY xdate);[/code]

Is there a way to do this without the subquery?

Cheers
-pnj

[fenway]

I guess you could self-join and squeeze in the other query that way...

[artacus]

We're only talking about 3 rows here. Just return it to an array in PHP and reverse the array.

[fenway]

Yes, but in the general case, that's not the ideal solution...

[artacus]

... no the ideal solution is to use a subquery

[fenway]

[i]Narf! Point! Egad![/i]  It sounded like the poster wanted an SQL solution without subqueries, hence my suggestion...

[artacus]

He DID. The point I was making (unsucessfully, judging by your sarcasm) was that pnj ruled out the ideal solution.

[fenway]

[quote author=artacus link=topic=119373.msg489287#msg489287 date=1166659970]
He DID. The point I was making (unsucessfully, judging by your sarcasm) was that pnj ruled out the ideal solution.
[/quote]
Your point was valid -- IMHO, "valid" includes "practical" under the poster's circumstances.

[pnj]

Thanks y'all,

Both solutions are helpful.

-pnj