MS SQL Script Optimisation

[phobos]

Still a bit of a newbie to SQL, I've written a solution to a problem but I'm sure there is a neater fix.

I have two tables,

Table 1 (sales) which indexes product_id, customers and the date when the customer bought the product.
contains the columns Prod_Id (int), Customers (char), DateNo (int)

Table 2 (products) contains a list of all products in the system
contains the columns Prod_Id(int), ProductDescript ion(char) and Price (int)


I want to display a list to the top 10 most popular products.
Listing their description, price and the number of times they have been purchased in 2006. Does not matter who bought them.

My current approach is:
-----------------------------------------------------------------------

CREATE TABLE #temptable
(
Prod_Id INT NOT NULL,
Counter INT NOT NULL
)

INSERT INTO #temptable
SELECT TOP 10 WITH TIES Prod_Id, Count(*)
FROM Sales
WHERE DateNo >= '20060101'
AND DateNo < '20060516'
GROUP BY Prod_Id
ORDER BY COUNT(*) DESC;

SELECT t.Counter, p.ProductDescri ption, p.Price
FROM Products AS p, #temptable AS t
WHERE t.Prod_Id = p.Prod_Id;

DROP TABLE #temptable

------------------------------------------------

Any help would be appriciated.
Thank you.

[JohnK]

I havent tested this, as I'm lasy, but it should give the same result but a bit faster as its not creating and dropping tables


SELECT TOP 10 WITH TIES p.ProductDescri ption, p.Price ,Count(s.Prod_I d)
FROM Sales as s
INNER JOIN Products AS p
ON s.Prod_Id = p.Prod_Id
WHERE s.DateNo >= '20060101'
AND s.DateNo < '20060516'
GROUP BY p.ProductDescri ption, p.Price
ORDER BY Count(s.Prod_Id ) DESC

Hope it works