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

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` — , Nov 12 '18 at 10:20
Thanks for the duplicate questions. I tried searching as well but could not find them. — Hussain Ali Akbar, Nov 12 '18 at 10:26

score 0 · Answer 1 · answered Nov 12 '18 at 10:18

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 '18 at 10:18

Fahmi

37,315
5
22
31

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. – Hussain Ali Akbar Nov 12 '18 at 10:26

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

1 Answers1