By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

MS SQL Script Optimisation

P: 1
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), ProductDescription(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.ProductDescription, 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.
May 17 '06 #1
Share this Question
Share on Google+
1 Reply


P: 2
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.ProductDescription, p.Price ,Count(s.Prod_Id)
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.ProductDescription, p.Price
ORDER BY Count(s.Prod_Id) DESC

Hope it works
Jul 19 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.