1

I have a table called Inventory with the below columns 

 item     warehouse            date                       sequence number     value

  111       100         2019-09-25 12:29:41.000                1                10
  111       100         2019-09-26 12:29:41.000                1                20
  222       200         2019-09-21 16:07:10.000                1                5
  222       200         2019-09-21 16:07:10.000                2                10
  333       300         2020-01-19 12:05:23.000                1                4
  333       300         2020-01-20 12:05:23.000                1                5

Expected Output:

     item     warehouse            date                       sequence number     value

     111       100         2019-09-26 12:29:41.000                1                20
     222       200         2019-09-21 16:07:10.000                2                10
     333       300         2020-01-20 12:05:23.000                1                5

Based on item and warehouse, i need to pick latest date and latest sequence number of value.

I tried with below code

select item,warehouse,sequencenumber,sum(value),max(date) as date1
        from Inventory t1
where
 t1.date IN (select max(date) from Inventory t2
                where  t1.warehouse=t2.warehouse
              and t1.item = t2.item
              group by t2.item,t2.warehouse) 
            group by t1.item,t1.warehouse,t1.sequencenumber

Its working for latest date but not for latest sequence number.

Can you please suggest how to write a query to get my expected output.

Michael Bruesch
  • 632
  • 7
  • 23
rajini
  • 33
  • 4

1 Answers1

0

You can use row_number() for this:

select *
from (
    select 
        t.*, 
        row_number() over(
            partition by item, warehouse 
            order by date desc, sequence_number desc, value desc
        ) rn
    from mytable t
) t
where rn = 1
GMB
  • 216,147
  • 25
  • 84
  • 135