Solved

SQL SERVER 2005 - SELECT top 4 query

Posted on 2008-10-14
14
1,099 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
Comment Utility

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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 100 total points
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 400 total points
Comment Utility
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
Comment Utility
great mwvisa1: many thanks for your help !!

0
 

Author Comment

by:learningnet
Comment Utility
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
Comment Utility
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
Comment Utility
You would be better off to put that into a stored procedure and execute the stored procedure from your data adapter.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
OK thanks
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now