SQL SERVER 2005 - SELECT top 4 query

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                	

Select allOpen in new window

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

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

Select allOpen in new window

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.

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

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

great mwvisa1: many thanks for your help !!

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

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"

You would be better off to put that into a stored procedure and execute the stored procedure from your data adapter.

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

Select allOpen in new window

OK thanks