?
Solved

SQL SERVER 2005 - SELECT top 4 query

Posted on 2008-10-14
14
Medium Priority
?
1,143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
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 60

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 60

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 60

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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