SQL Query - how to filter dates rows

Question

I have tried to find an answer to my problem via the search option but I failed to find a 100% fit solution.

I have the next table output:

vpo_number  location_name   current_task_id VPO_TIME                Shelf_Time
Y046006F    FLOOR           971516          2020-11-30 05:00:32.560 2020-11-20 07:01:56.830
**Y046006F  FLOOR           971516          2020-11-30 05:00:32.560 2020-12-23 12:08:55.183**
Y046006F    FLOOR           971516          2020-11-30 05:00:32.560 2020-11-18 21:34:21.473

How can I edit my query that I will get only the row with the most recent update under "Shelf_Time"

Thanks,

See Why should I provide an MCRE for what seems to me to be a very simple SQL query — Strawberry
– Strawberry, Commented Jan 28, 2021 at 16:58

Gordon Linoff · Accepted Answer · 2021-01-28 16:24:31Z

3

The canonical method is to use row_number() to get one row per group. Assuming you want one row per vpo_number:

select t.*
from (select t.*,
             row_number() over (partition by vpo_number order by shelf_time desc) as seqnum
      from t
     ) t
where seqnum = 1;

If you just want the most recent row in the table, use order by and limit:

select t.*
from t
order by shelf_time desc
limit 1;

answered Jan 28, 2021 at 16:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL Query - how to filter dates rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related