Get most recent Price for each Item

Question

I have a table:

ItemID PurchaseDate Price  
001 03/17/2013 19.00  
002 03/17/2013 14.00  
001 03/18/2013 13.00  
002 03/18/2013 15.00  
001 03/19/2013 17.00  
003 03/19/2013 19.00

I need to write a SQL query to get the Price corresponding to the latest PurchaseDate for each ItemID. Entries in table might not necessarily be entered ordered by date Like this:

ItemID PurchaseDate Price  
001 03/19/2013 17.00  
002 03/18/2013 15.00  
003 03/19/2013 19.00

score 8 · Accepted Answer · edited Aug 25 '15 at 00:16

8

The idea behind the subquery is it separately gets the latest PurchaseDate for each ItemID. The result of the subquery is then joined back on the table provided that it matches on two conditions: ItemID and PurchaseDate.

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  ItemID, MAX(PurchaseDate) max_date
            FROM     TableName
            GROUP   BY ItemID
        ) b ON  a.ItemID = b.ItemID AND
                a.PurchaseDate = b.max_date

edited Aug 25 '15 at 00:16

Eric VB

103
2
7

answered Mar 19 '13 at 09:03

John Woo

258,903
69
498
492

1

Why are u so **QUICK** ? **:P** – Prahalad Gaggar Mar 19 '13 at 09:03
@JW No go in Access 2010, unfortunately: "Syntax error in JOIN operation." – Gord Thompson Mar 19 '13 at 09:50
2

@GordThompson it works in access. just put "TableName AS a" in fist FROM clause and "TableName AS b" in second FROM clause, oh, and "MAX(PurchaseDate) AS max_date". Gave CHECK to JW because his answer faster, and I used his query. Thanks JW. – Alin I Mar 19 '13 at 11:20
For the record: I submitted an edit to this answer to fix the syntax so it would *actually work* in Access but that edit was apparently rejected. – Gord Thompson Mar 19 '13 at 13:32

Rachcha · Answer 2 · 2013-03-19T10:12:44.310

3

-- WITH clause, works with Oracle.
-- I added this clause to dynamically run the SELECT statement without any DDL.
-- Ignore this section for use on MS Access
WITH v AS (
  SELECT 001 ItemID, TO_DATE('03/17/2013', 'MM/DD/YYYY') PurchaseDate, 19.00 Price FROM dual
  UNION ALL
  SELECT 002, TO_DATE('03/17/2013', 'MM/DD/YYYY'), 14.00 FROM dual
  UNION ALL
  SELECT 001, TO_DATE('03/18/2013', 'MM/DD/YYYY'), 13.00 FROM dual
  UNION ALL
  SELECT 002, TO_DATE('03/18/2013', 'MM/DD/YYYY'), 15.00 FROM dual
  UNION ALL
  SELECT 001, TO_DATE('03/19/2013', 'MM/DD/YYYY'), 17.00 FROM dual
  UNION ALL
  SELECT 003, TO_DATE('03/19/2013', 'MM/DD/YYYY'), 19.00 FROM dual
)
-- The WITH clause was upto here.
-- Below starts the main query which works on most platforms including MS Access.
-- I have referenced to the same table "v" two times - v_in and v_out.
-- You will need to change the "v" with your table name.
  SELECT v_out.itemid, v_out.purchasedate, v_out.price
    FROM v v_out
   WHERE EXISTS (SELECT 1
                   FROM v v_in
                  WHERE v_in.itemid = v_out.itemid
               GROUP BY v_in.itemid
                 HAVING MAX(v_in.purchasedate) = v_out.purchasedate)
ORDER BY v_out.itemid
;

edited Mar 19 '13 at 10:12

answered Mar 19 '13 at 09:11

Rachcha

8,486
8
48
70

@JW, common table expression? Would you please elaborate? – Rachcha Mar 19 '13 at 09:34
here http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx . did you really test your query? – John Woo Mar 19 '13 at 09:36
Yes! MS-ACCESS does allow `EXISTS` queries. Here: http://msdn.microsoft.com/en-us/library/office/aa217680%28v=office.11%29.aspx . I coded the `WITH` clause just to do all this in one single query and it works in Oracle. Alin I can use the main `SELECT` query as a reference though. The logic is perfect. – Rachcha Mar 19 '13 at 09:42
@Rachcha Yes, Access supports EXISTS but not WITH. The main SELECT query does indeed work in Access. You might consider editing your answer to clarify, in which case I will upvote. – Gord Thompson Mar 19 '13 at 10:06

Get most recent Price for each Item

2 Answers2

Linked