CODEWITHSUNDEEP
sqlpostgresqldategreatest-n-per-group
turtle_in_mind
turtle_in_mind

Reputation: 1152

how to pick the latest data in postgresql

i want to query my db with the latest record on file. When I try this: 

select distinct(ts) from my_table

I get two dates:

2020-03-10 22:54:08
2020-03-10 22:29:57

my db schema: 

Create table my_table
    (
        uuid text NULL,
        portfolio_family_id bigint NULL,
        ticker text NULL,
        size double precision NULL,
        secid bigint NULL,
        portfolio_name_id bigint NULL,
        ts timestamp NOT NULL DEFAULT now()
    );

you can have multiple repetitive uuids in the uuid column. I would like to get all the rows where ts is the latest data. How would i query this? 

select to_char(ts,'YYYY-MM-DD HH24:MI:SS') ts2 from my_table mt
inner join (select to_char(max(ts),'YYYY-MM-DD HH24:MI:SS') t2 from 
my_table) c2 on c2.t2 = mt.ts2

I get error: column ts2 doesn't exist. Hint: Perhaps you mean to reference mt: ts?

I want all records pertaining to this date: 2020-03-10 22:29:57

Upvotes: 0

Views: 341

Answers (1)

GMB
GMB

Reputation: 222402

If you want the latest row per uuid, then:

select distinct on (uuid) *
from mytable
order by uuid, ts desc

If you want all rows that correspond to the latest date available in the table, then:

select t.*
from mytable t
where t.ts = (select max(t1.ts) from mytable t1)

You can get the same result with window functions:

select (s.t).*
from (select t, rank() over(order by ts desc) rn from mytable t) s
where rn = 1

Upvotes: 1

Related Questions