Query to elimate multiple rows with multiple dates

Question

I have a table:-

SKU      DATE        VALUE
ABC      2 Aug 10    100
ABC      15 Aug 10   150
ABC      20 Aug 10   180
DEF      2 Aug 10    130
GHI      3 Aug 10    120
JKL      2 Aug 10    140
JKL      20 Aug 10   160

I want it to be:-

SKU      DATE        VALUE
ABC      20 Aug 10   180
DEF      2 Aug 10    130
GHI      3 Aug 10    120
JKL      20 Aug 10   160

In short, remove those SKUs with multiple dates based on oldest date. Those with single date, retain.

Thanks.

Answer 1

A general solution in lieu of specific DBMS...

SELECT *
FROM yourTable T1
WHERE NOT EXISTS (
  SELECT *
  FROM yourTable T2
  WHERE T2.SKU = T1.SKU
  AND T2.DATE > T1.DATE
)

Answer 2

In addition to @Will's answer, if you happen to be using MySQL or SQL Server (tested in 2008), you can use the simple multiple-table DELETE syntax as follows:

DELETE  t1
FROM    your_tb t1
JOIN    your_tb t2 ON (t2.sku = t1.sku AND t2.date > t1.date);

Test case:

CREATE TABLE your_tb (sku char(3), date date, value int);

INSERT INTO your_tb VALUES ('ABC', '2010-08-02', 100);
INSERT INTO your_tb VALUES ('ABC', '2010-08-15', 150);
INSERT INTO your_tb VALUES ('ABC', '2010-08-20', 180);
INSERT INTO your_tb VALUES ('DEF', '2010-08-02', 130);
INSERT INTO your_tb VALUES ('GHI', '2010-08-03', 120);
INSERT INTO your_tb VALUES ('JKL', '2010-08-02', 140);
INSERT INTO your_tb VALUES ('JKL', '2010-08-20', 160);

Result after delete is applied:

SELECT * FROM your_tb;
+------+------------+-------+
| sku  | date       | value |
+------+------------+-------+
| ABC  | 2010-08-20 |   180 |
| DEF  | 2010-08-02 |   130 |
| GHI  | 2010-08-03 |   120 |
| JKL  | 2010-08-20 |   160 |
+------+------------+-------+
4 rows in set (0.00 sec)

Answer 3

what if there are multiple rows with same sku and same date? which one you want to select?

(i did not see a link to put comments against your question, hence replying as answer).

SKU date value
ABC 4-aug-2010 10
ABC 4-aug--2010 20
ABC 4-aug-2010 40

Answer 4

In some databases you can use analytic functions for this:

Select sku, date, value from (
    select t.*,
        row_number() over (partition by sku order by date) row
    from t1    
) where row = 1

Warning: probably contains typos.

This should work on oracle an possibly on sql server.