CODEWITHSUNDEEP
mysqlsql
Avi22
Avi22

Reputation: 57

SQL Query - how to filter dates rows

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,

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 3

Related Questions