Select up to x rows of each group.

Question

With table:

id | name | job | rank 01 john teacher 4 02 mark teacher 2 03 phil plummer 1 04 dave teacher 7 05 jim plummer 9 06 bill plummer 2 

How can I select up to two lines of each task (if possible, sorted by ASC rank in each group, so that the lowest two ratings of each group will be selected). The result that I would like to find:

 02 mark teacher 2 01 john teacher 4 03 phil plummer 1 06 bill plummer 2 

This is mainly grouped by assignment with a limit of 2 and sorted by rank. I tried with GROUP BY as well as with LEFT JOIN, but I just can't figure out how to do this. When creating a "temporary list" of tasks with a GROUPING BY task, how do I join this task more than once?

Answer 1

 SELECT id, name, job, rank FROM TableName a WHERE ( SELECT COUNT(*) FROM TableName as f WHERE f.job = a.job AND f.rank <= a.rank ) <= 2; 

• SQLFiddle Demo