How to get Distinct Value in Postgresql?

Question

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

Md. Golam Mostafa · Accepted Answer · 2021-03-28 16:47:45Z

1

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

answered Mar 28, 2021 at 16:47

Md. Golam Mostafa

362 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2021-03-28 05:06:13Z

1

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.

answered Mar 28, 2021 at 5:06

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

How to get Distinct Value in Postgresql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related