CODEWITHSUNDEEP
sqldb2
Bob
Bob

Reputation: 885

How to pick the latest row

I have two tables, namely Price List (Table A) and Order Record (Table B):-

Table A

SKU Offer Date  Amt
AAA 20120115    22
AAA 20120223    24
AAA 20120331    25
AAA 20120520    28

Table B

A001       AAA  20120201
B001       AAA  20120410
C001       AAA  20120531

I have to retrieve the latest pricing for each customer. The expected output should be like this:-

Customer  SKU   Order Date  Amt
A001      AAA   20120201    28
B001      AAA   20120410    28
C001      AAA   20120531    28

Thanks.

Upvotes: 0

Views: 216

Answers (1)

user1166147
user1166147

Reputation: 1610

Here is T-SQL - not sure what you are running, add that as a tag in your questions for better answers - Wrote this before the edit of the OP, so double check the cols.

EDITED per x-zeros' comment

SELECT  B.CUSTOMER,S.SKU,B.ORDERDATE,S.Amt
FROM TABLE_B B 
INNER JOIN 
(   SELECT C.SKU,C.OFFERDATE,C.Amt, 
    ROW_NUMBER() OVER (PARTITION BY C.SKU ORDER BY C.OFFERDATE DESC) X
    FROM TABLE_A C
)S ON S.X = 1 AND B.SKU = S.SKU
ORDER BY B.CUSTOMER


CREATE TABLE TABLE_A 
(SKU varchar(8), OfferDate Date, Amt int)
INSERT INTO TABLE_A
VALUES('AAA', '2012-01-15', 22),
      ('AAA' ,'2012-02-23', 24),
      ('AAA' ,'2012-03-31', 25),
      ('AAA' ,'2012-05-20', 28),
      ('BBB','2011-01-15 00:00:00.000', 33),
      ('BBB','2011-02-23 00:00:00.000', 35),
      ('BBB','2011-03-31 00:00:00.000', 36),
      ('BBB','2011-05-20 00:00:00.000', 39),
      ('CCC', '2012-01-15', 43),
      ('CCC' ,'2012-02-23', 45),
      ('CCC' ,'2012-03-31', 47),
      ('CCC' ,'2012-04-18', 44)

CREATE TABLE TABLE_B 
(CUSTOMER varchar(8),SKU varchar(8), OrderDate Date)
INSERT INTO TABLE_B
VALUES('A001','AAA','2012-02-01'),
      ('B001','AAA','2012-04-10'), 
      ('C001','AAA','2012-05-31'),
      ('A001','BBB','2011-02-01'),
      ('B001','BBB','2011-04-10'),
      ('C001','BBB','2011-05-31'),
      ('B001','CCC','2011-04-10'),
      ('C001','CCC','2011-05-31')

Upvotes: 1

Related Questions