Interesting SQL Problem (Example Data Provided)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Interesting SQL Problem (Example Data Provided)

Author

Topic

pinecrest515
Starting Member

11 Posts

Posted - 2011-02-06 : 16:44:51

Dear Forumers,

I would like to consult you on an interesting problem, and hopefully brush up on my SQl skills.

I am trying to get a report to output the top 10 sales items by dept, category, and sub-category level. To illustrate the problem without using actual company data, I created a sample problem and data set (see below).

Problem: Suppose I have a listing of player ratings in basketball and soccer (under Sports category) and U.S./European leagues (under League category). I want to get the following:
1) Highest 2 rated players in each league for each sport
2) Highest 2 rated players in each sport
3) Highest 2 rated players overall

How do I construct the SQL? I want to display the highest 2 rated players in each league for each sport together in the same output so that they can be compared. Ideally, since the real data is huge, I don't want to construct a separate view just for ranking purposes.

Sample data:

Area Sport League Player Rating
Sports B-Ball NBA Duncan 90
Sports B-Ball NBA James 99
Sports B-Ball NBA Wade 97
Sports B-Ball NBA Bryant 99
Sports B-Ball Euro Rubio 83
Sports B-Ball Euro Navarro 85
Sports B-Ball Euro Lull 79
Sports B-Ball Euro Vasquez 78
Sports Soccer MLS Donovan 81
Sports Soccer MLS Beckham 80
Sports Soccer MLS Henry 82
Sports Soccer MLS Marquez 78
Sports Soccer Liga Messi 99
Sports Soccer Liga Iniesta 91
Sports Soccer Liga Xavi 90
Sports Soccer Liga Ronaldo 96

Ideally, the result should look like this:
Sports B-Ball NBA James 99
Sports B-Ball NBA Bryant 99
Sports B-Ball Euro Rubio 83
Sports B-Ball Euro Navarro 85
Sports Soccer MLS Donovan 81
Sports Soccer MLS Henry 82
Sports Soccer Liga Messi 99
Sports Soccer Liga Ronaldo 96

Will this work?
SELECT Top 2 Rating,
Area,
Sport,
League,
Player
FROM
Table
WHERE Area = 'Sports'
ORDER BY Rating

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 16:56:26

Challenge accepted. :) here is one way perhaps. Not really that tough with the newer functionality.


DECLARE @foo table ( 
	Area char(6) not null,
	Sport char(6) not null,
	League char(5) not null,
	Player char(7) not null,
	Rating int not null)


INSERT INTO @foo

SELECT 'Sports','B-Ball', 'NBA' ,'Duncan', 90 
UNION SELECT 'Sports','B-Ball', 'NBA' ,'James', 99
UNION SELECT 'Sports','B-Ball', 'NBA' ,'Wade', 97
UNION SELECT 'Sports','B-Ball', 'NBA' ,'Bryant', 99
UNION SELECT 'Sports','B-Ball', 'Euro', 'Rubio', 83
UNION SELECT 'Sports','B-Ball', 'Euro', 'Navarro', 85
UNION SELECT 'Sports','B-Ball', 'Euro', 'Lull', 79
UNION SELECT 'Sports','B-Ball', 'Euro', 'Vasquez', 78
UNION SELECT 'Sports','Soccer', 'MLS' ,'Donovan', 81
UNION SELECT 'Sports','Soccer', 'MLS' ,'Beckham', 80
UNION SELECT 'Sports','Soccer', 'MLS' ,'Henry' ,82
UNION SELECT 'Sports','Soccer', 'MLS' ,'Marquez', 78
UNION SELECT 'Sports','Soccer', 'Liga', 'Messi' ,99
UNION SELECT 'Sports','Soccer', 'Liga', 'Iniesta', 91
UNION SELECT 'Sports','Soccer', 'Liga', 'Xavi' ,90
UNION SELECT 'Sports','Soccer', 'Liga', 'Ronaldo', 96

;WITH cteRanks AS (
SELECT 
Area,Sport,League
,Player,Rating,RN = RANK() OVER (PARTITION BY Area,Sport,League ORDER BY Rating desc)
FROM @Foo  )

SELECT * FROM cteRanks where RN <=2

Poor planning on your part does not constitute an emergency on my part.

pinecrest515
Starting Member

11 Posts

Posted - 2011-02-06 : 17:08:48

Hi dataguru,

Thanks for the reply. Your method is definitely valid; however, the challenge I have is that I am in real life dealing with nationwide sales data for a large company. The sample sports related scenario and data I created to illustrate the problem is a very simplified version.

In order to find the top 10 items by a store department (my real life problem case), for example, I would have to SELECT ... RN = RANK() for each item in the store across all stores. That's a huge table I am creating

SELECT * FROM
(SELECT Dept, Item, Sales_Amt RN = RANK () OVER (PARTITION BY Dept, Item ORDER By Sales_Amt) FROM Sales_Table) As RK
WHERE RK.RN <= 10

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 17:42:18

With proper indexing, large tables aren't a problem. I use CTE's do things like this on millions and millions of records.

This kind of thing is done all the time. They didn't make CTE functionality and ranking functions just for small tables.

Sorry I couldn't help you get closer to the real world solution you were looking for.

Poor planning on your part does not constitute an emergency on my part.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-07 : 04:18:06

quote:
In order to find the top 10 items by a store department (my real life problem case), for example, I would have to SELECT ... RN = RANK() for each item in the store across all stores. That's a huge table I am creating

How big is "huge"? Huge is a relative term so it would be beneficial to know ballpark figures for number of rows we are talking about. If we are talking hundreds of millions of rows ranking data like this might take a while...

- Lumbago
My blog-> www.thefirstsql.com

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 04:59:05

quote:
Originally posted by pinecrest515

Thanks for the reply. Your method is definitely valid; however, the challenge I have is that I am in real life dealing with nationwide sales data for a large company. The sample sports related scenario and data I created to illustrate the problem is a very simplified version.

In order to find the top 10 items by a store department (my real life problem case), for example, I would have to SELECT ... RN = RANK() for each item in the store across all stores. That's a huge table I am creating

it seems that now you have no issue as far as the display is concerned and now its a space Space or Performance related issue for you ?.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources