This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Dont you think, this is a very common requirement for most of the Database Developer.
Yes, MySQL Developer can visit this article.
In PostgreSQL, We can get a first record for each GROUP using different options like:
- Using DISTINCT ON
- Using LATERAL
- CTE with ROW_NUMBER()
- CTE with LATERAL
- Subquery with ROW_NUMBER()
- Using array_agg()
In this demonstration, I am going to give two optimized solutions to get one record per each GROUP.
Create a table with Sample records:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE tbl_Employees ( EmpID INT ,EmpName CHARACTER VARYING ,EmpDepartment CHARACTER VARYING ,EmpSalary INT ); INSERT INTO tbl_Employees VALUES (1,'Anvesh','Database',90000) ,(2,'Jenny','JAVA',65000) ,(3,'Martin','PHP',85000) ,(4,'Roy','PHP',94000) ,(5,'Eric','PHP',70000) ,(6,'Rajesh','Animation',50000) ,(7,'Mahi','Database',40000) ,(8,'Sofia','JAVA',60000); |
First Solution using DISTINCT ON:
As per the Postgres official document,
The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).
The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.
1 2 3 4 |
SELECT DISTINCT ON (EmpDepartment) * FROM tbl_Employees ORDER BY EmpDepartment ,EmpSalary DESC; |
A LATERAL allows sub-queries to reference columns provided by preceding FROM items.
1 2 3 4 5 6 7 8 9 10 |
SELECT DISTINCT T.* FROM tbl_Employees e ,LATERAL ( SELECT * FROM tbl_Employees WHERE EmpDepartment = e.EmpDepartment ORDER BY EmpSalary DESC LIMIT 1 ) AS T; |
The Result of both the solution :
1 2 3 4 5 6 |
empid | empname | empdepartment | empsalary -------+---------+---------------+----------- 6 | Rajesh | Animation | 50000 1 | Anvesh | Database | 90000 2 | Jenny | JAVA | 65000 4 | Roy | PHP | 94000 |