CODEWITHSUNDEEP
sqlpostgresqldategreatest-n-per-group
lightningmanic
lightningmanic

Reputation: 2165

SQL selecting earliest date of subgroup

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?

Upvotes: 0

Views: 1794

Answers (5)

Jakub Kania
Jakub Kania

Reputation: 16487

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

Upvotes: 1

maringan
maringan

Reputation: 195

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

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

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.

Upvotes: 0

VJ Hil
VJ Hil

Reputation: 904

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

Upvotes: 1

Lamak
Lamak

Reputation: 70638

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

Upvotes: 1

Related Questions