CODEWITHSUNDEEP
sqlselectgroup-bymaxsql-delete
taox
taox

Reputation: 55

How to delete smaller records for each group?

My table price has current schema:

item date         shift     price1  price2
1    20110723     day       40      50
1    20110723     night     42      52
1    20110723     weekend   42      52
2    20110723     Night     40      50
...

And I want to keep the records with highest price1 or price2 for each item in the table, i.e., remove records with either smaller price1 or price2 from the table. How to write the sql?

I use sybase ASE 12.5

Upvotes: 1

Views: 241

Answers (2)

Andriy M
Andriy M

Reputation: 77737

In SQL Server (and probably some other RDBMSes) you can use a CTE and ranking, like this:

WITH ranked AS (
  SELECT
    *,
    price1rank = RANK() OVER (PARTITION BY item ORDER BY price1 DESC),
    price2rank = RANK() OVER (PARTITION BY item ORDER BY price2 DESC)
  FROM Test1
)
DELETE FROM ranked
WHERE price1rank <> 1
  AND price2rank <> 1;

UPDATE

Posting the entire testing script, including the above DELETE statement, so anyone willing can play with it or just verify whether it works:

CREATE TABLE Test1 (
  item int,
  date date,
  shift varchar(30),
  price1 money,
  price2 money
);
GO
INSERT INTO Test1 (item, date, shift, price1, price2)
SELECT 1, '20110723', 'day    ', 40, 50 UNION ALL
SELECT 1, '20110723', 'night  ', 42, 52 UNION ALL
SELECT 1, '20110723', 'weekend', 42, 52 UNION ALL
SELECT 2, '20110723', 'Night  ', 40, 50;
GO
SELECT * FROM Test1
GO
WITH Test1Ranked AS (
  SELECT
    *,
    price1rank = RANK() OVER (PARTITION BY item ORDER BY price1 DESC),
    price2rank = RANK() OVER (PARTITION BY item ORDER BY price2 DESC)
  FROM Test1
)
DELETE FROM Test1Ranked
WHERE price1rank <> 1
  AND price2rank <> 1;
GO
SELECT * FROM Test1
GO
DROP TABLE Test1
GO

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

you can write following query, if you are working in oracle -

delete from item_table where rowid not in
(
     select rowid from item_table 
     where (item,price1) in (select item,max(price1) from item_table group by item)
        or (item,price2) in (select item,max(price2) from item_table group by item)
)

i heard that rowid is not there in sql server or mysql ... please tell us about your database name which one you are using.

you can write as follow also..

delete from item_table where (item,date,shift,price1,price2 ) not in
    (
        select item,date,shift,price1,price2  from item_table 
        where (item,price1) in (select item,max(price1) from item_table group by item)
           or (item,price2) in (select item,max(price2) from item_table group by item)
    )

Upvotes: 0

Related Questions