Solved

SQL SERVER 2005 - SELECT top 4 query

Posted on 2008-10-14
14
1,130 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 59

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 100 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 59

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 59

Accepted Solution

by:
Kevin Cross earned 400 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 59

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 33
SQL Throw Error 7 35
SQL Select Query help 1 38
T-SQL: How to extract records into a new table 7 21
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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

733 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