how to fetch other values along with distinct clause in postgres? [duplicate]

Question

I have the following postgres table workExperiences:

id | userId | experience | isCurrentWorkplace 1 | 1 | xyz | true 2 | 1 | abc | true 3 | 1 | wxy | false 4 | 2 | qwe | false 5 | 2 | xyz | true 6 | 3 | abc | true

I want to fetch unique userIds along with the id of the table where isCurrentlyWorkplace is set to true. As each user can have multiple records with isCurrentWorkPlace to true, i am getting duplicate records.

Basically, this is the result set that i want: id | userId | experience | isCurrentWorkplace 1 | 1 | xyz | true 5 | 2 | xyz | true 6 | 3 | abc | true

The latest record of each user where isCurrentWorkplace is set to true.

The query works fine like this and returns distinct userIds:

select distinct ("userId") from "workExperiences" where "isCurrentWorkplace" = true;

but when i select id as well, i start to get duplicate records:

select distinct ("userId"), id from "workExperiences" where "isCurrentWorkplace" = true;

how can i achieve this?

Thanks for the help in advance!

distinct is NOT a function. distinct (a),b is the same as distinct a,(b) or distinct a,b — user330315
– user330315, Commented Nov 12, 2018 at 10:20
Thanks for the duplicate questions. I tried searching as well but could not find them. — Hussain Ali Akbar
– Hussain Ali Akbar, Commented Nov 12, 2018 at 10:26

Fahmi · Accepted Answer · 2018-11-12 10:18:01Z

0

You can try using row_number()

select * from
(
select *, row_number() over(partition by userid order by id) as rn
from "workExperiences" where "isCurrentWorkplace" = true
) A where rn=1

answered Nov 12, 2018 at 10:18

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

1 Comment

Hussain Ali Akbar Over a year ago

Thanks for the help. i'll check this out as well . But please do check the duplicate answer as well. Its much more simpler in my opinion.

Collectives™ on Stack Overflow

how to fetch other values along with distinct clause in postgres? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related