How to pick the latest row

Question

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.

Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format or [table dump](http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html). Please use `CREATE TABLE` and `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Jun 14 '12 at 02:48
Agree with outis - I can't understand what you're trying to do. — lxop, Jun 14 '12 at 02:51
possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/q/612231/), [SQL: Select most recent date for each category](http://stackoverflow.com/q/6680568/) — outis, Jun 14 '12 at 02:52
Added DB2 to tags based on your comment to my TSQL answer, trying to get you better answer. — user1166147, Jun 14 '12 at 14:00
At least your dates are in a sortable format, but how are they actually stored (are they a `date` type)? Also, what version of DB2? And you're retreiving the 'latest pricing', but apparently for orders that were made when the price was different - this makes it _appear_ like you may be mis-charging customers (maybe give us a little more explanation of the context here?). — Clockwork-Muse, Jun 14 '12 at 15:53

score 1 · Accepted Answer · edited Jun 14 '12 at 19:05

1

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')

edited Jun 14 '12 at 19:05

Clockwork-Muse

12,806
6
31
45

answered Jun 14 '12 at 03:44

user1166147

1,570
2
15
17

It keeps saying SKU not is table S in *N. BTW, I'm using DB2. – Bob Jun 14 '12 at 04:54
Don't know about DB2 - Sounds like it is trying to pull the wrong column or name. Your column names were not included in B - though S is A. Try breaking it down and running the inner query, replacing column names, then running more as you fix it. And again, don't know DB2 syntax - might need some tweaks - a quick search did say `Rank over partition ` is supported. – user1166147 Jun 14 '12 at 13:57
Sorry, this won't get correct results, if different SKUs have pricing changes on different dates (happens all the time in a retail environment). Is there a reason for the `SELECT MAX()` bit? I would have just used the reference with the `RANK()` function. Specifying _both_ `DISTINCT` and `GROUP BY` is unneccesary, and I have a feeling neither is actually needed here. – Clockwork-Muse Jun 14 '12 at 15:59
Thanks @X-Zero - The DISTINCT, etc was left over from testing different things and copy/paste - Removed. I am seeing the results expected from my understanding of the question though with SKU price changes - Fix it! (Please) I threw in some creates and some inserts. Just learned about 'Rank over partition' - show me the error of my ways – user1166147 Jun 14 '12 at 17:27
1

Hmm, I think I prefer `ROW_NUMBER()` over `RANK()` for this, althou assuming `offerDate` is unique per SKU, it won't make a difference. Other than that, all that needs to be done is get the 'top' row per SKU. The sub-sub-select wasn't performing any useful work, removed it. Statement runs as-is on my version of DB2. – Clockwork-Muse Jun 14 '12 at 19:02
Cool. Thanks for the taking the time. – user1166147 Jun 14 '12 at 19:17

How to pick the latest row

1 Answers1