SQL selecting earliest date of subgroup

Question

I have a table which looks like the following:

| ID | Person | Modified On |

If I have the following records:

{id: 1, person: 'John', Modified On: 2014-06-01 12:00:00}, {id: 2, person 'John', Modified On: 2014-06-02 12:00:00}, {id: 2, person 'Kate', Modified On: 2014-06-02 12:08:00}, {id: 2, person 'Sarah', Modified On: 2014-06-02 12:02:00}, {id: 2, person 'Sarah', Modified On: 2014-06-01 12:00:00}

Notice that the same person "John" and Sarah is in there twice. Modified once on June 1, and again on June 2.

What I'd like is to be able to select one of each person, but only their earliest dates. So my results should be:

{id: 1, person: 'John', Modified On: 2014-06-01 12:00:00}, {id: 2, person 'Kate', Modified On: 2014-06-02 12:08:00}, {id: 2, person 'Sarah', Modified On: 2014-06-01 12:00:00}

How should I construct my SQL to do so?

[Another one of those ...](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564) — Erwin Brandstetter, Jun 06 '14 at 22:16

VJ Hil · Answer 1 · 2014-06-06T20:17:48.220

1

---- Use the analytic function rank() so that it returns the rank of a value in a group of values.With clause will hep you create a temporary set of data.

WITH TEMP AS
(
SELECT id, person, Modified_On,
rank() OVER (PARTITION BY person ORDER BY Modified_On) AS RK
FROM TABLE1
)
SELECT id, person, Modified_On FROM TEMP WHERE RK=1;

edited Jun 06 '14 at 20:17

answered Jun 06 '14 at 20:11

VJ Hil

904
6
15

1

I think the order by on `Modified_On` should not be DESC, cause then it will pull the record with the highest date. – user2989408 Jun 06 '14 at 20:13

score 1 · Answer 2 · answered Jun 06 '14 at 20:12

1

SELECT *
FROM (  SELECT  *,
                ROW_NUMBER() OVER(PARTITION BY person
                                  ORDER BY "Modified On") AS RN
        FROM YourTable) AS X
WHERE RN = 1

answered Jun 06 '14 at 20:12

Lamak

69,480
12
108
116

score 1 · Answer 3 · answered Jun 06 '14 at 20:29

1

The correct way to select only one row per group in PostgreSQL is DISTINCT ON clause. Although technicaly simple MIN() would do in this case.

SELECT DISTINCT ON (person) id, person, modified_on,
FROM TABLE1
ORDER BY person, modified_on

answered Jun 06 '14 at 20:29

Jakub Kania

15,665
2
37
47

score 0 · Answer 4 · answered Jun 06 '14 at 20:19

0

Try:

select *
from my_table t1
where "Modified On" = (select min("Modified On") from my_table t2 where t1.person = p2.person)

This uses a subselect to get the min date for each person.

answered Jun 06 '14 at 20:19

Vulcronos

3,428
3
16
24

score 0 · Answer 5 · answered Jun 06 '14 at 20:26

0

SELECT id, person, min(Modified On) FROM some_table
Group by person

answered Jun 06 '14 at 20:26

maringan

195
10

Explaining a bit your answer would be nice. :) – gsamaras Jun 06 '14 at 20:46

SQL selecting earliest date of subgroup

5 Answers5