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

Question

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 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!!!

Answer 1

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;