January 26, 2012 at 10:24 am
Hi,
I have a product database from different brands their are 120 brands and 80,000 products each product has a popularity count maintained by a column named popularity, now I want to write a select query which will select top 40 products from all these brands based on popularity count.
I would appreciate help.
Thank you
January 26, 2012 at 10:27 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 5:36 pm
This demonstrates one technique:
CREATE TABLE #Example
(
BrandId integer NOT NULL,
ProductId integer NOT NULL,
Popularity integer NOT NULL,
PRIMARY KEY (BrandId, ProductId)
);
CREATE INDEX nc1 ON #Example (BrandId, Popularity DESC);
INSERT #Example
(BrandId, ProductId, Popularity)
VALUES
(1, 1, 500),
(1, 2, 400),
(1, 3, 300),
(1, 4, 200),
(1, 5, 100),
(2, 1, 50),
(2, 2, 90),
(2, 3, 30),
(2, 4, 20),
(2, 5, 40);
SELECT
Top3.*
FROM
(
SELECT DISTINCT
e.BrandId
FROM #Example AS e
) AS Brands
CROSS APPLY
(
SELECT TOP (3) *
FROM #Example AS e2
WHERE e2.BrandId = Brands.BrandId
ORDER BY e2.Popularity DESC
) AS Top3
ORDER BY
Brands.BrandId,
Top3.Popularity DESC;
DROP TABLE #Example;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 12:40 am
SQL Kiwi (1/26/2012)
This demonstrates one technique:
CREATE TABLE #Example
(
BrandId integer NOT NULL,
ProductId integer NOT NULL,
Popularity integer NOT NULL,
PRIMARY KEY (BrandId, ProductId)
);
CREATE INDEX nc1 ON #Example (BrandId, Popularity DESC);
INSERT #Example
(BrandId, ProductId, Popularity)
VALUES
(1, 1, 500),
(1, 2, 400),
(1, 3, 300),
(1, 4, 200),
(1, 5, 100),
(2, 1, 50),
(2, 2, 90),
(2, 3, 30),
(2, 4, 20),
(2, 5, 40);
SELECT
Top3.*
FROM
(
SELECT DISTINCT
e.BrandId
FROM #Example AS e
) AS Brands
CROSS APPLY
(
SELECT TOP (3) *
FROM #Example AS e2
WHERE e2.BrandId = Brands.BrandId
ORDER BY e2.Popularity DESC
) AS Top3
ORDER BY
Brands.BrandId,
Top3.Popularity DESC;
DROP TABLE #Example;
thanks a lot this works!
January 27, 2012 at 1:01 am
Glad to hear it. If you need more information on *how* it works, please see:
http://qa.sqlservercentral.com/articles/APPLY/69953/
http://qa.sqlservercentral.com/articles/APPLY/69954/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply