# MSSQL Group by then select top one
###### tags: `SQL`

Sql 要 group by 資料，然後選出前幾筆的的方式有兩種

* ROW_NUMBER()
ROW_NUMBER() over(partition by 分群欄位 order by 排序欄位 )

EX:

    select UserName, CreateDate, City
    from (
	    select UserName, CreateDate, City, ROW_NUMBER() over(partition by city order by CreateDate ) as row_index
	    from customer with (nolock)	
	    where CreateDate>'2020-10-01'
    ) as tempTable
    where tempTable.row_index = 1
    order by City

* Cross Apply和Outer Apply
思路: 逐一檢查表個中的每一筆，找出該資料城市創建最早的 Id，判斷出這資料是否是需要的
inner join 也可以做到一樣效果
但用 apply 會有較好的效能， join 比較注重兩張表的連接

EX:

    select a.UserName, a.CreateDate, a.City
    from customer a with (nolock)
    CROSS APPLY (
        select top 1 b.UserName, b.Id
        from customer b with (nolock)
        where a.City=b.City
        order by CreateDate
    ) c
    where a.Id=c.Id

### 參考連結
[ROW_NUMBER 範例](https://toyo0103.blogspot.com/2013/05/sqlgroup-by-and-top-1.html)
[Cross Apply和Outer Apply](https://www.itread01.com/content/1554457115.html)