Random_Display_Name Random_Display_Name - 2 months ago 6
PHP Question

SQL get first occurence and last

let suppose we got this kind of table :

UserID ID FLAG
1 1 red
2 2 white
3 1 white
4 2 green
5 2 Brown
6 5 White
7 1 Blue
8 3 Green
9 4 Green
10 2 Red
11 4 Green
12 3 Black


I want to get the first flag and the last for both ID. I tried to use min/max function but it only gave me the first and last row of the table . I tried to use "first" too but apparently it doesnt work with sql Server.

Desired Output for first occurence:

ID Flag
1 red
2 white
3 Green
4 Green
5 White


Desired Output For Last Occurence :

ID Flag
1 Blue
2 Red
3 Black
4 Green
5 White

Answer

You need to anchor a column which defines the sorting criteria The order we see on your sample data does not ensure that the sorting is as is in the table I added the "ident" identity column numbering rows from 1 to n..

Then you can use following sample script with SQL First_Value() function

--alter table flags add ident int identity(1,1)
select distinct
    id, 
    FIRST_VALUE(flag) over (partition by id order by ident) first,
    FIRST_VALUE(flag) over (partition by id order by ident desc) last
from flags

Output is as wanted enter image description here