In mySQL, how do I return a list of grouped items limited to a certain number of groups + unique items

Okay, so this one may be difficult but I will do my best to explain. Bear with me...

I have a table with multiple columns as follows.

ITEM_ID - GROUP_ID

1 - null

2 - null

3 - null

4 - 3

5 - 3

6 - 3

7 - 21

8 - 6

9 - 21

10 - 21

11 - 21

12 - 4

13 - 4

15 - 6

16 - 6

17 - 6

19 - 3

I want to go into this table and return a list such that:

1.) Each item is separate.

2.) Each item is grouped by the group ID.

3.) The result is limited to 5 groups total with each item that is null counted as a single unique group.

4.) When a group is returned, all objects that have the same group number are returned.

With this example I want to return:

1 - null

2 - null

3 - null

4 - 3

5 - 3

6 - 3

19 - 3

7 - 21

9 - 21

10 - 21

11 - 21

So items 1,2 and 3 each count as 1 returned group apiece for a total of 3 returned groups. Items 4,5,6 and 19 are all group 3开发者_JAVA技巧 and count as one returned group bringing our total to 4 groups returned. Items 7,9,10 and 11 are all group 21 and count as one returned group bringing our total to 5 returned groups at which point no more groups or single items (which count as groups) are returned.

I am usually pretty good with SQL but this one completely eludes me.

Thanks everyone!!!

This will return what you are asking for. The inner subqueries simply filter the nulls and assign a unique "key" (a negative id number). These are "limited" to the first five unique group ids and are then joined against the a copy of the subquery to return the records we need. The outer subquery is then used to print the result set in the manner reuquested.

select a.item_id, case when a.group_id <= 0 then null else a.group_id end group_id 
from (
    select distinct group_id, item_id from test.so_test where group_id is not null 
    union
    (select  -item_id, item_id from test.so_test where group_id is null)
)  a inner join (
    select distinct group_id from test.so_test where group_id is not null 
    union
    (select  -item_id from test.so_test where group_id is null)
    order by group_id limit 0, 5
) b on a.group_id = b.group_id
order by case when a.group_id <= 0 then null else a.group_id end, a.item_id;