Home postgresql select max no if date is same
Reply: 1

postgresql select max no if date is same

Deepak Kumar
1#
Deepak Kumar Published in 2017-12-07 16:29:43Z

i have a table like:

  sr_no    or_no     date
  1        1         2017-01-01  
  1        2         2017-02-02
  1        3         2017-02-02
  2        1         2017-01-02
  2        2         2017-01-10

What i want is if date field for a sr_no is same then record with max or_no should be fetched. Output should be like:

sr_no     or_no    date
1         1        2017-01-01
1         3        2017-02-02
2         1        2017-01-02
2         2        2017-01-10
Vao Tsun
2#
Vao Tsun Reply to 2017-12-07 17:11:55Z

just distinct on to cut max(or_no) with order:

with a as (select distinct on (d) * from t order by d,or_no desc)
select * from a order by sr_no,or_no;
 sr_no | or_no |     d
-------+-------+------------
     1 |     1 | 2017-01-01
     1 |     3 | 2017-02-02
     2 |     1 | 2017-01-02
     2 |     2 | 2017-01-10
(4 rows)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.334923 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO