Picking up ID when grouping

Question

Suppose we have the following data set.

ID C1 C2
1 - a - a
2 - b - b
3 - a - a
4 - a - b
5 - a - b
6 - b - b

I can execute the following statement:

select C1, C2 from Hazaa
group by C1, C2

However, I'd like to be able to get the value from ID column as well. Of course, I understand it's not possible just like that. My condition is that it's the lowest ID value in each groupings members that is listed.

I've tried googling for that but I think I'm expressing my request in a clumsy way because Google gives me nada, at least as far I can understand. The best hit is this one but, frankly, I'm not sure I understand how it's done. (I need it to work on 2005 but in the future we'll move to 2012 so if there's a neater trick there, I'd like to learn both ways.)

from the linked question; `first()` is not a valid SQl Server aggregate function, if you just want the lowest numerical ID, just use `MIN()`. — KM., Sep 16 '14 at 12:53

KM. · Accepted Answer · 2014-09-16T12:46:54.237

you need to specify which ID you want from within the group, this will show the first and the last for each group:

SELECT min(ID),max(ID),count(ID),C1, C2 from Hazaa group by C1, C2

you'll want min(ID), but look at the other values so you can understand what is happening.

group by combines multiple rows in to a single one, you use aggregate functions like min(), max(), count() to get values per each group.

Sample code:

declare @Hazaa table (ID int,c1 char(1), c2 char(1))
insert into @Hazaa values (1 , 'a' , 'a')
insert into @Hazaa values (2 , 'b' , 'b')
insert into @Hazaa values (3 , 'a' , 'a')
insert into @Hazaa values (4 , 'a' , 'b')
insert into @Hazaa values (5 , 'a' , 'b')
insert into @Hazaa values (6 , 'b' , 'b')

SELECT 
    min(ID) AS MinID,max(ID) AS MaxID,count(ID) AS CountOf,C1, C2 
    from  @Hazaa group by C1, C2

OUTPUT:

      MinID       MaxID     CountOf C1   C2
----------- ----------- ----------- ---- ----
          1           3           2 a    a
          4           5           2 a    b
          2           6           2 b    b

(3 row(s) affected)

Picking up ID when grouping

1 Answers1