CODEWITHSUNDEEP
sqlpostgresql
user2967527
user2967527

Reputation: 13

How to select multiple distinct values and max values in postgres?

I have a table which logs activity on a particular rowid. Sample data is

rowid   activityid  status
323767  241288  Compromise
323767  227405  Untraced
323767  226356  Untraced
323767  226218  Reinvestigation ordered by court
323767  226217  Untraced
319859  225642  Reinvestigation ordered by court
319859  222568  Reinvestigation ordered by court
319807  222551  
319807  222524  
305458  204104  Reinvestigation ordered by court
305458  204002  Convicted

I need to get the max value of activityid for a single rowid..like as below

rowid   activityid  status
323767  241288  Compromise
319859  225642  Reinvestigation ordered by court
319807  222551  
305458  204104  Reinvestigation ordered by court

Upvotes: 1

Views: 121

Answers (2)

zhaobing
zhaobing

Reputation: 11

you can try this

select tableA.rowid,tableA.activityid,table.status
from  (select rowid,max(activityid)  as activityid 
        from table 
       group by rowid) tableA,table
where tableA.activityid = table.activityid

Upvotes: 0

user330315
user330315

Reputation:

A solution solution based on standard ANSI SQL would be using a co-related sub-query

select t1.rowid, 
       t1.activityid,
       t1.status
from the_table t1
where t1.activityid = (select max(t2.activityid) 
                       from the_table t2
                       where t1.rowid = t2.rowid)
order by rowid;

or (more efficiently) a window function:

select *
from (
  select rowid, 
         activityid,
         max(activityid) over (partition by rowid) as max_activity,
         status
  from the_table 
) t 
where activityid = max_activity
rowid;

however in Postgres this can be done more efficiently using distinct on operator

select distinct on (rowid) rowid, activityid, status
from the_table
order by rowid, activityid desc

The solution with distinct on is going to be the fastest, followed by the solution using a window function and the co-related subquery is most probably going to be the slowest.

Upvotes: 3

Related Questions