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:
CREATE TABLE tbl_Employees
,EmpName CHARACTER VARYING
,EmpDepartment CHARACTER VARYING
INSERT INTO tbl_Employees
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.
SELECT DISTINCT ON (EmpDepartment) *
ORDER BY EmpDepartment
Second Solution using LATERAL:
A LATERAL allows sub-queries to reference columns provided by preceding FROM items.
SELECT DISTINCT T.*
FROM tbl_Employees e
WHERE EmpDepartment = e.EmpDepartment
ORDER BY EmpSalary DESC
) AS T;
The Result of both the solution :
empid | empname | empdepartment | empsalary
6 | Rajesh | Animation | 50000
1 | Anvesh | Database | 90000
2 | Jenny | JAVA | 65000
4 | Roy | PHP | 94000
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.