CODEWITHSUNDEEP
postgresql
Pirinthan
Pirinthan

Reputation: 524

How to get most recently date in postgreSQL?

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

see the out put for my query

Upvotes: 1

Views: 128

Answers (1)

jpw
jpw

Reputation: 44871

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

Upvotes: 1

Related Questions