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

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!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