how to select first row order by date from a group by

You could use a subquery with min date for city in join with you table

select m.* from my_table m inner join ( select city, min(date) as min_date from my_table group by city ) t on t.city = m.city and t.min_date = m.date 

You can use this approach, it will work faster.

select City,[date],Person from CTE a where [date] = (select min([date]) from CTE as b where b.City = a.City); 

You can use TOP 1 WITH TIES

select top 1 with ties City ,date ,Person from MyTable order by row_number() over (partition by City order by date asc) 

SQL Fidddle

Using First_Value () In Sql server

;WITH CTE(City,[date],Person ) AS ( SELECT 'A','2018/05/01','peter' UNION ALL SELECT 'A','2018/03/01','Jack' UNION ALL SELECT 'B','2018/02/16','TOM' UNION ALL SELECT 'C','2018/03/01','Mike' ) SELECT DISTINCT City, FIRST_VALUE([date])OVER (PARTITION BY City ORDER BY [date]) AS [date], FIRST_VALUE(Person)OVER (PARTITION BY City ORDER BY [date])AS Person FROM CTE 

Result,Demo: http://rextester.com/DLPE49383

City date Person -------------------------- A 2018/03/01 Jack B 2018/02/16 TOM C 2018/03/01 Mike 

select City, min(date) as date, Person from T group by City, Person 

Use ROW_NUMBER

WITH A (City, [Date], Person) AS ( SELECT 'A' , CAST('2018-05-01' AS DATE) , 'Peter' UNION ALL SELECT 'A' , CAST('2018-03-01' AS DATE) , 'Jack' UNION ALL SELECT 'B' , CAST('2018-02-16' AS DATE) , 'TOM' UNION ALL SELECT 'C' , CAST('2018-03-01' AS DATE) , 'Mike' ) SELECT City, [Date], Person FROM ( SELECT ROW_NUMBER () OVER (PARTITION BY City ORDER BY [Date]) Rn , * FROM A ) P WHERE P.RN = 1