Postgres Select one record per matching condition

user_7726727 0
Viewed 59

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

postgresql
1 Answers

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
user_39842210
Related