Delete from PC table the computers having minimal hdd size or minimal ram size

Question

PC(code, model, speed, ram, hd, cd, price)

I am writing the query

delete from PC  
where exists
(select model , min(hd), min(ram) from PC
group by model)

but not getting correct result

Expected result is

 code   model   speed   ram hd  cd  price
    2   1121    750 128 14.0    40x 850.0000
    4   1121    600 128 14.0    40x 850.0000
    5   1121    600 128 8.0 40x 850.0000
    6   1233    750 128 20.0    50x 950.0000
    8   1232    450 64  8.0 24x 350.0000
    11  1233    900 128 40.0    40x 980.0000

Can someone correct me in my query ?

Answer 1

Your EXISTS will just delete anything from the table where the EXISTS condition is true. You can find out more here.

You need to delete only the records you're after, which points to a window function. You can find out more info here.

If you're just trying to delete the record with the lowest hd and ram then the below should help you on your way. Beware the below will order first by hd and then by ram. So the record with the smallest hd will always be deleted first.

BEGIN TRAN;
DELETE p FROM PC p
INNER JOIN
(
SELECT Code,
ROW_NUMBER() OVER (PARTITION BY model ORDER BY hd DESC, ram DESC) [RNum]
) m ON m.Code = p.Code AND m.RNum = 1;
--COMMIT TRAN;
--ROLLBACK TRAN;