SQL Query - return last n records

smcelhinney

This is probably staring me straight in the face. Im using MySQL to power an online football league, and I need a SQL statement to return last 3 records from database (to show a snapshot of the relegation zone muah muah muah muah )

Anyone know the statement? Dont even know where to start. Got the leaders one alright. (SELECT * FROM players ORDER BY points ASC LIMIT 3) straightforward enough..

Help!

is_that_so

smcelhinney wrote:

This is probably staring me straight in the face. Im using MySQL to power an online football league, and I need a SQL statement to return last 3 records from database (to show a snapshot of the relegation zone muah muah muah muah )

Anyone know the statement? Dont even know where to start. Got the leaders one alright. (SELECT * FROM players ORDER BY points ASC LIMIT 3) straightforward enough..

Help!

SELECT * FROM players ORDER BY points DESC LIMIT 3 ?

smcelhinney

I thought the same. But it then starts with the bottom record, then second-last, then third last.

Which kinda gives the wrong message in a relegation dogfight.. : )

Evil Phil

SELECT * FROM players ORDER BY points DESC LIMIT 3

and just reverse the result set in memory. What language is the site developed in? I doubt its difficult.

akari no ryu

Then just reverse the returned array, surely.

kayos

Totally a MS SQL answer but meh

SELECT 
	D.* 
FROM 
	(
	SELECT TOP 3 
		T.* 
	FROM 
		<Table> AS T 
	ORDER BY 
		T.Points ASC
	) AS D 
ORDER BY 
	D.Points DESC

That will reverse the order for ya.

Really dont use MySQL so dont know if you can do anything like that. I'm guessing no as the only person I know that used MySQL recently started on MS SQL and thought the above feature was the best thing since sliced bread.

smcelhinney

Ya, could do that. But am using mixture of JNDI/JSTL, and dont want scriptlets all over the page.

Would also like to do it in SQL, as it means I can move all SQL queries to stored procedures when JSTL finally catches up with MySQL 5.0.

Will keep looking, presumably its sub-queries. I dont know any MySQL functions that do it off hand.

If anyone gets an epiphany in the meantime, greatly appreciate it.

Cheers
Stephen