CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Zakir Hossain
Zakir Hossain

Reputation: 444

How to get Distinct Value in Postgresql?

I have a table approval like below..

m_requisition_id ad_wf_activity_id bpname designation
1014546 1014546 Abul Kalam Azad Asst. Manager
1014546 1225097 Md. Hasan Zahir Plant Manager
1014546 1229239 Md. Hasan Zahir Plant Manager
1014546 1229240 Sayela Alam Sr. Manager
1014546 1229241 Md. Hasan Zahir Plant Manager
1014546 1229242 Sayela Alam Sr. Manager

Now i need bpname column's distinct value based on ad_wf_activity_id column's maximum number like below.

m_requisition_id ad_wf_activity_id bpname designation
1014546 1014546 Abul Kalam Azad Asst. Manager
1014546 1229241 Md. Hasan Zahir Plant Manager
1014546 1229242 Sayela Alam Sr. Manager

Upvotes: 0

Views: 41

Answers (2)

Md. Golam Mostafa
Md. Golam Mostafa

Reputation: 36

You can use max() function instead of distinct on like below

select m_requisition_id, max(ad_wf_activity_id), bpname, designation
from approval
GROUP BY m_requisition_id, bpname, designation

Lets try here- https://dbfiddle.uk/?rdbms=postgres_12&fiddle=73f15e1ed96f1308a5984a91b1df91e2

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247950

That would be DISTINCT ON:

SELECT DISTINCT ON (m_requisition_id, bpname, designation)
       m_requisition_id, bpname, designation
FROM atable
ORDER BY m_requisition_id, bpname, designation, ad_wf_activity_id DESC;

Your data model is not normalized, you you are prone to problems caused by slightly different spellings.

Upvotes: 1

Related Questions