CODEWITHSUNDEEP
sqlpostgresqlmaxdistinct
user3580480
user3580480

Reputation: 482

Max column value, based on another column value

I have a table in Postgres and I am trying to return a Max column value, based on a unique value in another cell

My table currently looks like this.

 |---------------------|------------------|
 |      endtime        |       woid       |
 |---------------------|------------------|                 
 |    1515224469053    |         1        |
 |---------------------|------------------|                 
 |    1515224472342    |         1        |
 |---------------------|------------------|
 |    1515224459092    |         2        |
 |---------------------|------------------|                 
 |    1515224429053    |         2        |
 |---------------------|------------------|
 |    1515224402345    |         2        |
 |---------------------|------------------|                 
 |    1515224465033    |         3        |
 |---------------------|------------------|

...I need to return the Max value in the endtime field, where the woid field is unique.

So the resulting table looks like this:

 |---------------------|--------------|------------------|
 |      endtime        |     woid     |    Max(endtime)  |
 |---------------------|--------------|------------------|              
 |    1515224469053    |       1      |   1515224472342  |
 |---------------------|--------------|------------------|                 
 |    1515224472342    |       1      |   1515224472342  |
 |---------------------|--------------|------------------|
 |    1515224459092    |       2      |   1515224459092  |
 |---------------------|--------------|------------------|                 
 |    1515224429053    |       2      |   1515224459092  |
 |---------------------|--------------|------------------|
 |    1515224402345    |       2      |   1515224459092  |                  
 |---------------------|--------------|------------------|                 
 |    1515224465033    |       3      |   1515224465033  |
 |---------------------|--------------|------------------|

Upvotes: 0

Views: 83

Answers (2)

jimmu
jimmu

Reputation: 1056

Group by woid and join to get corresponding max_endtime.

select t1.endtime, t1.woid, t2.max_endtime
from table t1
join (select woid, max(endtime) as max_endtime
     from table t2
     group by woid) t2
on t1.woid = t2.woid

Upvotes: 0

vol7ron
vol7ron

Reputation: 42109

http://sqlfiddle.com/#!17/46fcd/1

SELECT *, MAX(endtime) OVER(PARTITION by woid) FROM times;

Upvotes: 1

Related Questions