SQL query to show most recent qty, but grouped by customer

Question

Item Number   |   Customer   |   Creation Date   |   Onhand Qty   
  123               1              03-FEB-19            654
  234               3              03-FEB-19            987
  789               5              03-FEB-19            874
  321               4              03-FEB-19            147
  567               7              03-FEB-19            632
  123               1              29-JAN-19            547
  234               3              29-JAN-19            814
  789               5              29-JAN-19            458
  321               4              29-JAN-19            330
  567               7              29-JAN-19            118

I have this data set above, but for thousands of items and hundreds of customers.

What I'd like to do is to just return the latest 'Onhand Qty' field, so max(creation_date) but by item and customer.

    Item Number   |   Customer   |   Creation Date   |   Onhand Qty   
  123               1              03-FEB-19            654
  234               3              03-FEB-19            987
  789               5              03-FEB-19            874
  321               4              03-FEB-19            147
  567               7              03-FEB-19            632

Effectively, I'm trying to find the most recent onhand qty amount, by customer and item, so I can say that at the most recent check, 'Customer 1 had 654 units of Item 123'.

Is someone able to help me?

This is in an Oracle database (V11).

Many thanks

Specify the expected result as well. (Having the above table data.) — jarlh, Feb 14 '19 at 10:55

score 0 · Answer 1 · answered Feb 14 '19 at 10:55

0

use row_number()

select * from (select *,row_number() over(partition by Customer order by creation_date desc,qty desc) rn from table
) t where t.rn=1

answered Feb 14 '19 at 10:55

Zaynul Abadin Tuhin

31,407
5
33
63

GMB · Accepted Answer · 2019-02-14T11:04:44.447

Use ROW_NUMBER() as follows :

SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY Customer, Item_Number ORDER BY creation_date DESC) rn
    FROM mytable t
) WHERE rn = 1

In the subquery, ROW_NUMBER() assigns a sequence number to each record in groups of records having the same Customer/Item. The sequence is ordered by descending creation date (so the highest date comes first). Then, the outer query filters on the first record in each group.

This DB Fiddle demo with your sample data returns :

ITEM_NUMBER | CUSTOMER | CREATION_DATE | ONHAND_QTY | RN
----------: | -------: | :------------ | ---------: | -:
        123 |        1 | 29-JAN-19     |        547 |  1
        234 |        3 | 29-JAN-19     |        814 |  1
        321 |        4 | 29-JAN-19     |        330 |  1
        789 |        5 | 29-JAN-19     |        458 |  1
        567 |        7 | 29-JAN-19     |        118 |  1

Guys, you absolutely smashed it. THANKS! All the suggestions were similar, and it has worked really well. Thanks all. — LarryGythgoe, Feb 15 '19 at 12:14

score 0 · Answer 3 · answered Feb 14 '19 at 10:58

0

You can try using row_number() and add partition by Customer,item order by creation_date desc in over clause

select * from 
(
select *,row_number() over(partition by Customer,item order by creation_date desc) rn from table
)A where rn=1

answered Feb 14 '19 at 10:58

Fahmi

37,315
5
22
31

SQL query to show most recent qty, but grouped by customer

3 Answers3