[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL SERVER 2005 - SELECT top 4 query

Posted on 2008-10-14
14
Medium Priority
?
1,150 Views
Last Modified: 2012-05-05
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
Comment
Question by:learningnet
  • 6
  • 4
  • 4
14 Comments
 

Author Comment

by:learningnet
ID: 22712272

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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22712387
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22712401
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 total points
ID: 22712410
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22712418
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
 

Author Comment

by:learningnet
ID: 22712714
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22712770
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
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 1600 total points
ID: 22713362
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
 

Author Comment

by:learningnet
ID: 22718850
great mwvisa1: many thanks for your help !!

0
 

Author Comment

by:learningnet
ID: 22719025
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
 

Author Comment

by:learningnet
ID: 22719055
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22720891
You would be better off to put that into a stored procedure and execute the stored procedure from your data adapter.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22720924
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
 

Author Comment

by:learningnet
ID: 22721908
OK thanks
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question