[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1149
  • Last Modified:

SQL SERVER 2005 - SELECT top 4 query

Hello Experts,

I have a SELECT statement which gives me the top 4 products from the productranking table using below
      
SELECT TOP (4) ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg,
Product.ProdName FROM ProductRanking INNER JOIN Product
 ON ProductRanking.ProdID = Product.ProdID
ORDER BY ProductRanking.Ranking

I am now trying to get top 4 products picking top 2 from each category.
Please see below for the desired result


LORDLADYGIFTPACK            1      Name.gif              Star      
ROSEGIFTPACK                    2      LairD.gif              Laird
WINELEARNIT                         4      NameD.gif      Rose      
WORLDCOOKINGLEARNIT     5      Adopt.gif        AVine      

I hope someone can help me in making the desired SQL for me?

Many thanks in advance

Regards
Kay
0
learningnet
Asked:
learningnet
  • 6
  • 4
  • 4
2 Solutions
 
learningnetAuthor Commented:

ProductRanking
--------------
ProdId				Ranking  
------				-------
LORDLADYGIFTPACK              	1	
ROSEGIFTPACK                  	2	
ROSEGIFTPACK                  	3	
WINELEARNIT                   	4	
WORLDCOOKINGLEARNIT           	5	
THEMINDLEARNIT                	6	
 
 
Category
--------
CatID	ProdId
-----   -------		
PERS	LORDLADYGIFTPACK              		
PERS	ROSEGIFTPACK                  	
PERS	ROSEGIFTPACK                  	
LEAR	WINELEARNIT                   	
LEAR	WORLDCOOKINGLEARNIT           	
LEAR 	THEMINDLEARNIT                	

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
WITH productCTE AS (
SELECT ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg,
Product.ProdName, row_number() OVER (PARTITION BY Category.CatID ORDER BY ProductRanking.Ranking) AS categoryRanking, Category.CatID
FROM (ProductRanking INNER JOIN Product
 ON ProductRanking.ProdID = Product.ProdID)
INNER JOIN Category ON Category.ProdID = Product.ProdID
)
SELECT TOP (4) *
FROM productCTE
WHERE categoryRanking <= 2
0
 
BrandonGalderisiCommented:
Try this.  You may need to fix the location of catID.
select * from ( 
SELECT ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg, row_number() over (partition by catid order by ProductRanking.Ranking) rn
Product.ProdName FROM ProductRanking INNER JOIN Product
 ON ProductRanking.ProdID = Product.ProdID
)a 
where rn<=2

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
BrandonGalderisiCommented:
if you use the CTE with statement, and you are executing this as a standalone SQL statement, you must have a ; before with.

ie:

;WITH productCTE AS (
0
 
Kevin CrossChief Technology OfficerCommented:
This is creating a ranking over the category ID using row_number() so that you can restrict each category to only contributing maximum of 2 products.

If you will have data with more than the two categories, you can still order by ranking in this piece of the T-SQL:
SELECT TOP (4) *
FROM productCTE
WHERE categoryRanking <= 2
ORDER BY ranking

This will ensure you take the 4 top ranking products and the where clause ensures you don't have more than two from any category.
0
 
learningnetAuthor Commented:
hello mwvisa1:

thanks for your help, this really has worked it seems

WITH productCTE AS (
SELECT ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg,
Product.ProdName, row_number() OVER (PARTITION BY ProductCategory.CatID ORDER BY ProductRanking.Ranking) AS categoryRanking,ProductCategory.CatID
FROM (ProductRanking INNER JOIN Product
 ON ProductRanking.ProdID = Product.ProdID)
INNER JOIN ProductCategory ON ProductCategory.ProdID = Product.ProdID
)
SELECT TOP (4) *
FROM productCTE
WHERE categoryRanking <= 2
ORDER BY ranking

i am sorry but there are more than categories in the productcategory table, i only wanted whether catid='PERS' and catid='LEAR'

please can you advise me the change here?



BrandonGalderisi:

thanks for your comments too, i am sorry i did not get what you mean by

;WITH productCTE AS (

thanks
0
 
BrandonGalderisiCommented:
BrandonGalderisi:

thanks for your comments too, i am sorry i did not get what you mean by

;WITH productCTE AS (


I was providing clarification for mwvisa1's solution in http:#22712387
0
 
Kevin CrossChief Technology OfficerCommented:
Here is updated query with your filter (it can be added to inside the CTE or outside.  I chose outside for this example.  What Brandon is indicating is that if SQL statements preceed the WITH statement it needs to be terminated by ; first so typically you will see my solution written as such to be safe.
;WITH productCTE AS (
SELECT ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg,
Product.ProdName, row_number() OVER (PARTITION BY ProductCategory.CatID ORDER BY ProductRanking.Ranking) AS categoryRanking,ProductCategory.CatID
FROM (ProductRanking INNER JOIN Product
 ON ProductRanking.ProdID = Product.ProdID)
INNER JOIN ProductCategory ON ProductCategory.ProdID = Product.ProdID
)
SELECT TOP (4) *
FROM productCTE
WHERE categoryRanking <= 2 AND catid IN ('PERS', 'LEAR')
ORDER BY ranking

Open in new window

0
 
learningnetAuthor Commented:
great mwvisa1: many thanks for your help !!

0
 
learningnetAuthor Commented:
mwvisa1,

your SQL works just fine on the SQL Server but when I tried to use the same in the TableAdapter Query Configuration Wizard it gave me an error
"unable to parse query text"

please can you advise on this ?

thanks
kay
0
 
learningnetAuthor Commented:
and when i used this

WITH productCTE AS (SELECT ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg,Product.ProdName, row_number() OVER (PARTITION BY ProductCategory.CatID ORDER BY ProductRanking.Ranking) AS categoryRanking,ProductCategory.CatID FROM (ProductRanking INNER JOIN Product  ON ProductRanking.ProdID = Product.ProdID) INNER JOIN ProductCategory ON ProductCategory.ProdID = Product.ProdID) SELECT TOP (4) * FROM productCTE WHERE categoryRanking <= 2 AND catid IN ('PERS', 'LEAR') ORDER BY ranking  

i get the following error
"The OVER SQL construct or statement is not supported"
0
 
BrandonGalderisiCommented:
You would be better off to put that into a stored procedure and execute the stored procedure from your data adapter.
0
 
Kevin CrossChief Technology OfficerCommented:
You can either create this as a view/procedure on SQL Server so that you can use CTE OR change to this:

(although, I see the issue you are getting is with the OVER clause -- if so, then see if a proc/function/view will work for you)
SELECT TOP (4) *
FROM (SELECT ProductRanking.ProdID, ProductRanking.Ranking, ProductRanking.ProdImg,
Product.ProdName, row_number() OVER (PARTITION BY ProductCategory.CatID ORDER BY ProductRanking.Ranking) AS categoryRanking,ProductCategory.CatID
FROM (ProductRanking INNER JOIN Product
 ON ProductRanking.ProdID = Product.ProdID)
INNER JOIN ProductCategory ON ProductCategory.ProdID = Product.ProdID
) t
WHERE categoryRanking <= 2 AND catid IN ('PERS', 'LEAR')
ORDER BY ranking

Open in new window

0
 
learningnetAuthor Commented:
OK thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now