LIMIT results to LAST n rows? Subquery?

bunner_bob

I'm trying to retrieve the last 3 dates in a table. I should be able to SORT BY Date DESC LIMIT 3, then resort the array (using I forget which PHP function) - right? - But I thought there might be a slicker way to just do it in SQL - the equivalent of LIMIT 3 but off the end of the result set instead of the beginning.

I suppose if I knew the number of records I could use that:

LIMIT [count-3], [count]

Hmm - maybe I should try my first subquery. Gotta check if my client's host is SQL 4.1+...

Maybe something like:

LIMIT ((SELECT COUNT() FROM Dates) -3), (SELECT COUNT() FROM Dates)

...nope, that just blows up.

Any advice?

Roger_Ramjet

With a subquery

SELECT * FROM (SELECT * FROM table ORDER BY date_col DESC LIMIT 3) ORDER BY date_col ASC

bunner_bob

Huh - all I get is fatal errors. I stripped it down to this:

SELECT FROM (SELECT FROM Meetings)

(Meetings being the name of the table) and I still get fatal errors. I wonder if somehow my upgrade to 4.1.x didn't take...)

Roger_Ramjet

Roger Ramjet wrote:

With a subquery

SELECT * FROM (SELECT * FROM table ORDER BY date_col DESC LIMIT 3) ORDER BY date_col ASC

WHOOPS, me bad. :queasy:

SELECT * FROM (SELECT * FROM table ORDER BY date_col DESC LIMIT 3) AS t ORDER BY date_col ASC

bunner_bob

Yeah, baby!

So I guess you have to alias the subquery in at least certain cases.

Thanks again!

Roger_Ramjet

Yes, you always have to alias the subquery when it appears in the FROM clause.