CODEWITHSUNDEEP
sqldatabaseselectgroup-by
ChangeMyName
ChangeMyName

Reputation: 7408

In SQL, how to select minimum value of a column and group by other columns?

I have a lookup table below:

id  ref order
1   6   0
2   6   0
3   7   0
5   34  0
6   33  0
6   255 1
9   12  0
9   80  1
12  7   0
12  76  1
13  10  0
15  12  0
16  6   0
16  7   1
17  6   1
17  63  0
18  7   0
19  7   1
19  75  0
20  6   0
20  63  1

So in the lookup table (tab_lkp), it has column [id] (the IDs of entities), [ref] (the reference id that points to other entities in another table) and [order] (tells the order of reference, smaller order means higher priority).

My expectation is that, for each of the IDs, only one ref with the smallest order is selected. My code is (by following Phil's answer):

select id
,      ref
,      min_order = min(order)
from [dbo].[tab_lkp]
group by id, ref
order by id, ref

But the code doesn't work for me, the results still contains multiple records for each of the IDs:

id  ref order
1   6   0
2   6   0
3   7   0
5   34  0
6   33  0
6   255 1
9   12  0
9   80  1
12  7   0
12  76  1
13  10  0
15  12  0
16  6   0
16  7   1
17  6   1
17  63  0
18  7   0
19  7   1
19  75  0
20  6   0
20  63  1

Could you please let me know what is wrong with my code? And how should I achieve my goal?

Upvotes: 1

Views: 329

Answers (3)

JohnHC
JohnHC

Reputation: 11195

From an ANSI sql approach:

select x2.id, x2.ref, x2.order
from MyTable x2
inner join 
(
  select id, min(order) as min_order
  from MyTable
  group by id
) x1
on x1.id = x2.id
and x1.min_order = x2.order

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146449

or by using a subquery that does exactly what you state that you want, "for each of the IDs, only one ref with the smallest order is selected"

Select * from tab_lkp t
Where order = 
    (Select Min(order) from tab_lkp 
     where Id = t.Id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You would normally do this using row_number():

select t.*
from (select t.*, row_number() over (partition by id order by ref) as seqnum
      from [dbo].[tab_lkp] t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions