Need some help with dropping certain rows in a sql table **** HELP! ****

Need some help with dropping certain rows in a sql table HELP!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sydney.luu@gmail.com - 21 Mar 2008 01:53 GMT

Hello,

I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:

Current data:

Vendor Item Frequency Price
-----------------------------------------------------
AA 101    25 10.50
AA 102    10 20.50
AA 103    2 9.75
AA 104    2 10.99
AA 105    1 10.99

BB 101    25 10.50
BB 102    10    20.50
BB 103    2    9.75
BB 104    2    10.99
BB 105    2    21.25
BB 106    1    21.25
-----------------------------------------------------

Desired result:

Vendor Item Frequency Price
-----------------------------------------------------
AA 101    25 10.50
AA 102    10 20.50
AA 104    2 10.99

BB 101    25    10.50
BB 102    10    20.50
BB 105    2    21.25
-----------------------------------------------------

The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.

I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.

Thank you in advance!

Reply to this Message

Plamen Ratchev - 21 Mar 2008 03:36 GMT

Here is one way to accomplish this using a single statement (SQL Server
2005):

CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));

INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);

;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message