Postgres distinct query in two columns

user_14313451 0
Viewed 49

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
greatest-n-per-group postgresql
2 Answers

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;
user_3303150

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.

user_9092270
Related