This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration to select a distinct value for each column of a table in PostgreSQL.
You can get distinct values for each column using array_agg() which returns a result in array format because we are trying to build our result like One to Many relationships.
For further data analysis, we can use this array result which contains distinct values for a corresponding entity.
Below is a demonstration of this:
Create a table with sample records:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE tbl_EmployeeHistory ( EmpName text ,DepartmentName text ,Weeks text ); INSERT INTO tbl_EmployeeHistory VALUES ('Anvesh','Animation','Week 1') ,('Roy','Animation','Week 3') ,('Anvesh','Database','Week 2') ,('Anvesh','Animation','Week 4') ,('Martine','Product Design','Week 2') ,('Anvesh','Database','Week 3') ,('Martine','Animation','Week 1') ,('Martine','Animation','Week 3') ,('Roy','Animation','Week 4') ,('Roy','Database','Week 1') ,('Roy','Product Design','Week 2') ,('Martine','Product Design','Week 3'); |
Use array_agg with DISTINCT:
|
1 2 3 4 5 |
SELECT EmpName ,array_agg(DISTINCT DepartmentName) AS DepartmentName FROM tbl_EmployeeHistory GROUP BY EmpName; |
The Result:
|
1 2 3 4 5 6 |
empname | departmentname ---------+--------------------------------------- Anvesh | {Animation,Database} Martine | {Animation,"Product Design"} Roy | {Animation,Database,"Product Design"} (3 rows) |
Use array_agg with DISTINCT for multiple columns:
|
1 2 3 4 5 6 |
SELECT EmpName ,array_agg(DISTINCT DepartmentName) AS DepartmentName ,array_agg(DISTINCT Weeks) AS Weeks FROM tbl_EmployeeHistory GROUP BY EmpName; |
The Result:
|
1 2 3 4 5 6 |
empname | departmentname | weeks ---------+---------------------------------------+--------------------------------------- Anvesh | {Animation,Database} | {"Week 1","Week 2","Week 3","Week 4"} Martine | {Animation,"Product Design"} | {"Week 1","Week 2","Week 3"} Roy | {Animation,Database,"Product Design"} | {"Week 1","Week 2","Week 3","Week 4"} (3 rows) |
