SQL conditional GROUP BY: how to do this?

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 

This outputs the following:

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

And I would like to get the following conclusion that the by billId groups are not NULL:

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

How can i achieve this? By the way, I do not care about the ambiguous collection of grouping results.

Thank you 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 that meetingID will be returned if it belongs to the right group.

Answer 2

You can combine 2 queries, one of which contains groups in non-empty elements, and the other contains zero ones.