SQL首次出现，最后出现

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

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

If you are OK with defining the "min" as the alphabetically smallest flag name, and max as the alphabetically greatest flag name, then one option here is to just do a simple GROUP BY query:

SELECT ID,
       MIN(FLAG) AS minFlag,
       MAX(FLAG) AS maxFlag
FROM yourTable
GROUP BY ID

SQL tables represent unordered sets. Hence, there is no "first" and "last" row, unless another column specifies the rows.

Assuming you have such a column, say one called createdat, then you can use row_number(). For the "first" row:

select t.*
from (select t.*,
             row_number() over (partition by id order by createdat asc) as seqnum
      from t
     ) t
where seqnum = 1;

For the last, you would use desc instead of asc.

;WITH T AS
(
    SELECT 
        ID,
        Flag,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY UserID) AS MinPartNo,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY UserID Desc) AS MaxPartNo            
    FROM @tblTest
)
SELECT 
    ID,
    Flag AS FirstRows
FROM T
WHERE MaxPartNo=0

For Last Row:

SELECT 
    ID,
    Flag AS LastRows
FROM T
WHERE MinPartNo=1

It is not popular method, but if you don't have any identity column you can use ROW_NUMBER() ... ORDER BY (SELECT NULL)):

;WITH cte AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RN
    FROM YourTable
)

SELECT TOP 1 WITH TIES 
                        c.ID,
                        c.FLAG,
                        c1.FLAG
FROM cte c
CROSS JOIN cte c1
WHERE c.ID = c1.ID
ORDER BY ROW_NUMBER() OVER (PARTITION BY c.ID ORDER BY c.RN, c1.RN DESC)

Output:

ID  FLAG    FLAG
2   white   Red
1   red     Blue
3   Green   Black
4   Green   Green
5   White   White