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

Question

I have a table like:

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

Now I want to find the earliest person for each city. The result will be

A        2018/03/01   Jack
B        2018/02/16    TOM
C        2018/03/01   Mike

How to do it?

Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Vladimir Baranov, May 16 '18 at 08:37

score 5 · Answer 1 · answered May 16 '18 at 08:37

5

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

answered May 16 '18 at 08:37

ScaisEdge

131,976
10
91
107

rajat · Answer 2 · 2018-05-17T13:35:30.377

2

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);

edited May 17 '18 at 13:35

answered May 16 '18 at 08:49

rajat

31
4

Please edit your code to make it appear as code. See here for help on how to do that: https://stackoverflow.com/editing-help – byouness May 16 '18 at 08:58
@rajat, this query will return both records where city = 'A'. – hkravitz May 16 '18 at 10:17

score 1 · Answer 3 · answered May 16 '18 at 08:48

1

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

answered May 16 '18 at 08:48

Amit Kumar

5,888
11
47
85

score 1 · Answer 4 · answered May 16 '18 at 11:21

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

score 0 · Answer 5 · answered May 16 '18 at 08:39

0

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

answered May 16 '18 at 08:39

inon

1,689
1
19
34

2

grouping by City and Person will not give you the required result. given this data set you'll get the entire table using this. – hkravitz May 16 '18 at 08:50

score 0 · Answer 6 · answered May 16 '18 at 08:40

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

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

6 Answers6