Show the Top ‘N’ in a group inside an SQL query

June 25th, 2010 by Alan MacGregor Leave a reply »

I’ve been trying to work out how to produce this for a while as I didnt want PHP to do the calculations and increase the server strain, thankfully after much searching I have come across this very helpful tutorial

http://www.artfulsoftware.com/infotree/queries.php?&bw=1245#104

This allows me to show the top 5 results for each group in my code it looks quite similar to this

SELECT seriesid, comicid, number
FROM (
SELECT seriesid, comicid, number,
IF( @prev <> seriesid, @rownum := 1, @rownum := @rownum+1 ) AS rank,
@prev := seriesid
FROM comic t
JOIN (SELECT @rownum := NULL, @prev := 0) AS r
ORDER BY t.seriesid
) AS tmp
WHERE tmp.rank <= 5
ORDER BY seriesid, number, comicid;

This is an example that I was working on, it shows three columns (seriesid, comicid & number) from the table (comic)

Advertisement

Trackbacks /
Pingbacks

  1. Daniel

Leave a Reply