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?
CodePudding user response：
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.