[SOLVED] SQL Query

[hellouthere]

Hello again, Barand has recently given me a query for selecting the most recent record from a database...

 

	$lstsql = "SELECT p.pilot_id , p.name, MAX(r.report_id) AS latest
	FROM pilots AS p INNER JOIN reports AS r ON p.pilot_id = r.pilot_id
	WHERE p.pilot_id='$pid'
	GROUP BY p.name";

 

i now need to select the 5 most recent results... i have seen other ways of doing it but cannot reproduce them...

 

any help is appreiciated...

 

thanks in advance....

 

(also the correct spelling for appreiciated )

[shocker-z]

$lstsql = "SELECT p.pilot_id , p.name, MAX(r.report_id) AS latest
	FROM pilots AS p INNER JOIN reports AS r ON p.pilot_id = r.pilot_id
	WHERE p.pilot_id='$pid'
	GROUP BY p.name LIMIT 5";

 

That should do the trick

 

Regards

Liam

[hellouthere]

If there is only 1 row without the limit, how could applying a limit make more? Ive tested and it doesnt work...

 

Any1 else?

[shocker-z]

sorry try this

 

$lstsql = "SELECT TOP 5 p.pilot_id , p.name, MAX(r.report_id) AS latest
	FROM pilots AS p INNER JOIN reports AS r ON p.pilot_id = r.pilot_id
	GROUP BY p.name";

 

 

Liam

[obsidian]

I think this is what you're after:

SELECT p.pilot_id , p.name, r.report_id AS latest
FROM pilots AS p INNER JOIN reports AS r ON p.pilot_id = r.pilot_id
WHERE p.pilot_id='$pid'
ORDER BY r.report_id DESC
GROUP BY p.name
LIMIT 5;