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