unable to fetch wanted querie in postgres-CodePudding

Suppose I've next data

id cid vid firstname lastname dtype dname createdat
1  32   34  ramesh    kjk       t    check 2021-11-02 10:00:51.66395
1  32   34  ramesh    kjk       t    check 2021-11-01 10:00:51.66395
1  32   4   raj       kjk       c    check 2021-11-04 10:00:51.66395

excepted o/p:

id cid vid firstname lastname dtype dname createdat
1  32   34  ramesh    kjk       t    check 2021-11-02 10:00:51.66395
1  32   4   raj       kjk       c    check 2021-11-04 10:00:51.66395

need to fetch only the only latest record of each user(vid) and document type can be c or t

CodePudding user response：

use corelated subquery

  select t1.* from table_nam t1
    where t1.createdat=(select max(createdat) from table_nam t2 where t1.vid=t2.vid)

also you can use distinct on

select distinct on (vid) as vid,createdat,cid
from table_name
order by vid,createdat desc