Postgres Select one record per matching condition

Question

I have some issues while trying to get only one record per matching condition..

Let's suppose I have Certifications table with the following columns: Id, EmployeeId, DepartmentId, CertificationTitle, PassedDate

An employee can have more then one record in this table but I need to get only one record per employee (based on latest PassedDate)

SELECT Id, EmployeeId, CertificationTitle 
FROM certifications c 
ORDER BY EmployeeId, PassedDate DESC

From this select I need somehow to get only the first record for each EmployeeId. Does anyone have any ideas how I can achieve this, Is it possible? The Id is the Primary Key on the table, so it is different on each record. I need to keep all this columns specified in the Select query. The Group By didn't worked for me, or maybe I did it wrong...

S-Man · Accepted Answer · 2020-12-01 07:24:04Z

4

Use DISTINCT ON. This returns exactly the first ordered record of the group. You ordered correctly by PassedData DESC to get the most recent record first. The group for DISTINCT ON, naturally, is EmployeeID:

SELECT DISTINCT ON (EmployeeId),
    Id, 
    EmployeeId, 
    CertificationTitle 
FROM certifications c 
ORDER BY EmployeeId, PassedDate DESC

answered Dec 1, 2020 at 7:24

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Select one record per matching condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related