SQL conditional GROUP BY: how to do it?

Question

Let's say I have the following SQL query:

SELECT Meeting.id AS meetingId, Bill.id AS billId
FROM Meeting 
LEFT JOIN Bill ON Meeting.FK_BillId = Bill.id

That outputs the following:

meetingId | billId
------------------
     a    |  NULL
     b    |  NULL
     c    |     1
     d    |     1
     e    |     1
     f    |     2
     g    |     2

And I would like the following output, that groups by billId's that aren't NULL:

meetingId | billId
------------------
     a    |  NULL
     b    |  NULL
     c    |     1
     f    |     2

How can I achieve that? By the way, I am not concerned by the ambiguous meetingId of the grouped results.

Thanks for your help!

Answer 1

In SQL Server:

SELECT  meetingId, billid
FROM    (
        SELECT  ROW_NUMBER() OVER (PARTITION BY billId ORDER BY meetingID) AS rn,
                m.*
        FROM    mytable m
        ) q
WHERE   rn = 1 OR billid IS NULL

ANSI:

SELECT  MIN(meetingId), billid
FROM    mytable
WHERE   billid IS NOT NULL
GROUP BY
        billId
UNION ALL
SELECT  meetingId, billId
FROM    mytable
WHERE   billid IS NULL

MySQL:

SELECT  meetingId, billid
FROM    mytable
WHERE   billid IS NOT NULL
GROUP BY
        billId
UNION ALL
SELECT  meetingId, billId
FROM    mytable
WHERE   billid IS NULL

This is a trifle more efficient than MIN if you really don't care about what meetingID will be returned as long as it belongs to the right group.

Answer 2

You could union 2 queries, one of which does the groups in the non-null entries, and the other that gets the null ones.