Postgres distinct query in two columns

Question

I want to write a postgres query. For every distinct combination of (career-id and uid) I should return the entire row which has max time. This is the sample data

id  time    career_id   uid  content
1   100     10000        5     Abc
2   300      6           7     xyz
3   200     10000        5     wxv
4   150      6           7     hgr

Ans:

id  time    career_id   uid   content
2   300           6       7   xyz
3   200       10000       5   wxv

user330315 · Accepted Answer · 2021-06-17 11:34:24Z

1

this can be done using distinct on () in Postgres

select distinct on (career_id, uid) *
from the_table
order by career_id, uid, "time" desc;

answered Jun 17, 2021 at 11:34

user330315

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

Comments

Hans · Accepted Answer · 2021-06-17 11:54:23Z

0

You can use CTE's for this. Something like this should work:

WITH cte_max_value AS (
SELECT 
  career_id, 
  uid, 
  max("time") as max_time
FROM mytable
GROUP BY career_id, uid
)
SELECT DISTINCT t.* 
FROM mytable AS t
INNER JOIN cte_max_value AS cmv 
  ON t.uid = cmv.uid AND t.career_id = cmv.career_id AND t.time = cmv.max_time

The CTE gives you all the unique combinations of career_id and uid with the relevant maximum time, the inner join then joins the entire rows into this. I'm using if you get two rows with the same maximum time for the same combination of career_id and uid you will get two rows returned. If you don't want that you will need to find a strategy to resolve this.

Edit: Also the proposed solution by a_hrose_with_name's solution is far nicer and unless you need some level of compatibility with other servers (sadly syntax varies) you should use that instead.

edited Jun 17, 2021 at 11:54

answered Jun 17, 2021 at 8:20

Hans

2,9304 gold badges32 silver badges42 bronze badges

2 Comments

user330315 Over a year ago

Those dreaded backticks are invalid in standard SQL and Postgres.

Hans Over a year ago

Thank you for pointing this out. Also, I definitely prefer your solution which is far more elegant.

Collectives™ on Stack Overflow

Postgres distinct query in two columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related