How to get most recently date in postgreSQL?

Question

There is a guaccountid with 3 different guorderid values and different enddate. I need to get only the latest enddate's guorderid for the particular guaccountid.

select guaccountid,guorderid,enddate
from ord_entitlement
where guaccountid ='15031021282118408'
ORDER BY enddate DESC

jpw · Accepted Answer · 2015-08-07 00:51:57Z

1

I would use the row_number() window function:

SELECT   
  guaccountid, 
  guorderid,
  enddate
FROM (
  SELECT
    guaccountid, 
    guorderid,
    enddate, 
    row_number() over (
      partition by guaccountid 
      order by to_timestamp(enddate, 'YYYY-MM-DD"T"HH24:MI:SS') desc
    ) r
  FROM ord_entitlement
  WHERE guaccountid = '15031021282118408'
    AND substr(enddate, 1, 4) != '9999'
  ) src
WHERE r = 1;

This would return:

|       guaccountid |         guorderid |            enddate |
|-------------------|-------------------|--------------------|
| 15031021282118408 | 15031708485830901 | 2015-04-16T08:42:1 |

If you remove guaccountid = '15031021282118408' from the where clause you would get the latest record for any guaccountid.

Sample SQL Fiddle

answered Aug 7, 2015 at 0:51

jpw

45k6 gold badges71 silver badges90 bronze badges

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

2 Comments

Patrick Over a year ago

Isn't a window function in a sub-query a little over the top here? You are potentially doing a lot of work in the sub-query that is of no consequence to the output.

jpw Over a year ago

@Patrick Sure, it does a little bit more work - I wouldn't say a lot though, certainly not if the accountid is constrained - but in the end it gives the correct result with only the row with the most recent date and nothing else. I guess getting the desired data should be worth the extra processing.

Collectives™ on Stack Overflow

How to get most recently date in postgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related