1

I have a table that looks like the below:

ID ID2 Name
111 223 ABC
111 225 ABC
111 227 ABC
113 234 DEF
113 242 DEF
113 248 DEF
113 259 DEF
113 288 DEF

What I am trying to achieve is to mark the record that has the lowest value in the ID2 table in every ID1 group doing a select statement, e.g.:

ID1 ID2 Name R
111 223 ABC Y
111 225 ABC
111 227 ABC
113 234 DEF Y
113 242 DEF
113 248 DEF
113 259 DEF
113 288 DEF
116 350 GHI Y
116 356 GHI

How do I achieve this in a SELECT statement?

uncleb
  • 35
  • 4
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Dec 14 '21 at 20:57

1 Answers1

3

The window functions should to the trick . Use dense_rank() if you want to see ties.

Select *
      ,R = case when row_number() over (partition by ID1,Name order by ID2) = 1
                then 'Y'
                else '' 
           end
 From  YourTable

I should add... The window functions can be invaluable. They are well worth your time experimenting with them.

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66