How to select from postgresql table that return distinct values of particular columns with PK field-CodePudding

take example of below table where ID is PrimaryKey

ID	user	organization
1	abc	xyz
2	def	uvw
3	abc	xyz
4	def	xyz
5	def	uvw

I want to select from this table with DISTINCT values of user and organization along with the ID. So what I'm expecting is a result like one of the below. ie; either this

ID	user	organization
1	abc	xyz
2	def	uvw
4	def	xyz

or this

ID	user	organization
3	abc	xyz
5	def	uvw
4	def	xyz

How do I write a query for this ?

CodePudding user response：

CREATE temp TABLE test (
    ID int,
    _user text,
    _organization text
);

SELECT DISTINCT ON (_user, _organization)
    id,
    _user,
    _organization
FROM
    test
ORDER BY
    _user,
    _organization,
    id;

--desc

 SELECT DISTINCT ON (_user, _organization)
        id,
        _user,
        _organization
    FROM
        test
    ORDER BY
        _user,
        _organization,
        id DESC;

useful link: Select first row in each GROUP BY group?

CodePudding user response：

Hope this helps you.

select max(id), max(users), max(org) from tbl1 group by users,org;

or this

select min(id), min(users), min(org) from tbl1 group by users,org;