Get the top 2 sold without using temp table.

Hi
I ran into a small issue and have a mental block on how to figure it out
without using a temp table. what I am basically trying to do is retreive the
result set without the use of a temp table and use a derived table approach.
The scenario is I have a products table where in i have the list of products
that are sold. I am trying to track down those products the have the highest
sales (in $$). Mind you its the number sold. I am not interested in how many
got sold. I am interested in which had the top 5 highest sales.
Here is the DDL and the T-SQL Queries that I came up with:
Create table #Products (ProductID INT,ProductName VARCHAR(50),Price Money)
INSERT INTO #Products VALUES (145,'Tomatoes',230.00)
INSERT INTO #Products VALUES (145,'Tomatoes',130.00)
INSERT INTO #Products VALUES (145,'Tomatoes',100.00)
INSERT INTO #Products VALUES (145,'Tomatoes',200.00)
INSERT INTO #Products VALUES (150,'Tide',400.00)
INSERT INTO #Products VALUES (105,'Cheese',120.00)
INSERT INTO #Products VALUES (105,'Cheese',560.00)
INSERT INTO #Products VALUES (150,'Tide', 80.00)
--1). Query 1 Gets me the top 5 products which are the highest sold (in
number).
SELECT TOP 5 ProductID, ProductName, SUM(Price) AS Price,Count(ProductID) AS
TotalSold
FROM #Products
GROUP BY ProductID, ProductName
ORDER BY Count(ProductID) DESC
--2). Now I want those products that have the highest sales.($$$). so i used
a temp table to get the results.
--Get top 5 which are the highest sold (that means the sum of price for them
should be higher).
--Step 1.Get into a temp table
SELECT TOP 5 ProductID, ProductName, SUM(Price) AS Price,Count(ProductID) AS
TotalSold
INTO #Temp
FROM #Products
GROUP BY ProductID, ProductName
ORDER BY Count(ProductID) DESC
--Get the top sold
Select DISTINCT TOP 5 ProductID,ProductName,Price
from #temp
WHERE (SELECT count(*) from #temp E2
WHERE E2.Price > #temp.Price
) < 2
ORDER BY #temp.Price DESC
Drop table #temp
My question is there anyway I can avoid the temp table and the query 2 can
be written using a derived table?. I am thinking more like I have to use the
max and top clause together. Any rewrite would be helpful.
Thanks
Mwhat about this?
SELECT TOP 2 ProductID, ProductName, SUM(Price) AS
Price,Count(ProductID) AS
TotalSold
FROM #Products
GROUP BY ProductID, ProductName
ORDER BY SUM(Price) DESC
http://sqlservercode.blogspot.com/|||Beautiful way of rewriting. It works. so all it needs is a change is order
by. Thanks SQL.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1139496548.028813.239790@.f14g2000cwb.googlegroups.com...
> what about this?
> SELECT TOP 2 ProductID, ProductName, SUM(Price) AS
> Price,Count(ProductID) AS
> TotalSold
> FROM #Products
> GROUP BY ProductID, ProductName
> ORDER BY SUM(Price) DESC
> http://sqlservercode.blogspot.com/
>|||Though not very intuitive, you can use TOP 5 in the derived table as well:
SELECT TOP 2 *
FROM ( SELECT TOP 5 ProductID, SUM( Price ), COUNT( * )
FROM #Products
GROUP BY ProductID
ORDER BY COUNT( * ) DESC ) D ( ProductID, Price, Total )
ORDER BY Price DESC ;
Anith|||Thanks Anith. Even this is a good solution.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e9cm0sYLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Though not very intuitive, you can use TOP 5 in the derived table as well:
> SELECT TOP 2 *
> FROM ( SELECT TOP 5 ProductID, SUM( Price ), COUNT( * )
> FROM #Products
> GROUP BY ProductID
> ORDER BY COUNT( * ) DESC ) D ( ProductID, Price, Total )
> ORDER BY Price DESC ;
> --
> Anith
>