How to calculate ranks or position?

Question

How to calculate ranks or position?

Nuts

SSCrazy

Points: 2547
More actions
April 8, 2010 at 6:44 pm

#220299

Hi Guys
I have calculated top 20 tests from my table and here is the result
Test_name year
test1 2008
test2 2008
...
test20 2008
NOw I need to compare these these tests against previous year as in what was the rank of test1 in 2007 as compared to 2008?
How do I calculate their ranking in this table to make the comparisons?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Take a look at ROW_NUMBER in BoL. If you wanted to use a table variable or temp table you could do the same thing with an identity column but what you have is pretty simple so I would stick with ROW_NUMBER.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

I think I have a good idea of what you want, and I think that the RANK() function will do what you want, but there are several questions remaining. If you were to provide the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Nuts (4/8/2010)
Hi Guys
I have calculated top 20 tests from my table and here is the result
Test_name year
test1 2008
test2 2008
...
test20 2008
NOw I need to compare these these tests against previous year as in what was the rank of test1 in 2007 as compared to 2008?
How do I calculate their ranking in this table to make the comparisons?
Thanks

Do you want a coded answer?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nuts SSCrazy Points: 2547 More actions · Answer 4

I am not sure what you mean by the 'coded answer'

But I will surely post the ddl shortly

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

I second Wayne here... please post as

1. Create table scripts for your table.

2. insert into table scritps - some sample data that will look like your actual data

3. indexes/constraints on your tables.

4. DESIRED OUTPUT - some visual representation of this.

i tell u, once u provide these informations, u ll be amazed at the number of different solutions u get , WITH TESTED AND OPTIMIZED CODE. 🙂

To get more about this go thro this FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

Cheers!!

Nuts SSCrazy Points: 2547 More actions · Answer 6

Hi Friends

Here is the ddl

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [#temp11](

[test_name] [nvarchar](50) NULL,

[Calendar_year] [int] NULL,

[Calendar_quarter] [int] NULL,

[TotalCost] [money] NULL

) ON [PRIMARY]

INSERT INTO #temp11

(test_name, calendar_year, calendar_quarter,totalcost)

SELECT 'test1',2008,1,3444.20 UNION ALL

SELECT 'test2',2008,1,2000.20 UNION ALL

SELECT 'test3',2008,1,1000 UNION ALL

SELECT 'test1',2008,2,2000.20 UNION ALL

SELECT 'test2',2008,2,5500.10 UNION ALL

SELECT 'test3',2008,2,1020.10 UNION ALL

SELECT 'test1',2007,1,3700 UNION ALL

SELECT 'test2',2007,1,2500.20 UNION ALL

SELECT 'test3',2007,1,1100 UNION ALL

SELECT 'test1',2007,2,4000.20 UNION ALL

SELECT 'test2',2007,2,2600.10 UNION ALL

SELECT 'test3',2007,2,2900.10

Now I have got top 20 different tests in each qtr for 3 years

And Here is my desired output...

Test_name Rank_Q1_2008 Rank_Q1_2007 Rank_Q2_2008 Rank_Q2_2007

Test1 1 1 2 1

Test2 ...

.

And so on

I need to compare the ranks in all four quarters

I hope I have made myself a little more clear now 🙂

cheers

nguyennd SSCommitted Points: 1881 More actions · Answer 7

Try this SQL. But you can use Dynamin SQL.

SELECT TEST_NAME

,max(CASE WHEN Calendar_year = 2008 AND Calendar_quarter = 1 THEN ORDERS ELSE '' END) as Rank_Q1_2008

,max(CASE WHEN Calendar_year = 2007 AND Calendar_quarter = 1 THEN ORDERS ELSE '' END) as Rank_Q1_2007

,max(CASE WHEN Calendar_year = 2008 AND Calendar_quarter = 2 THEN ORDERS ELSE '' END) as Rank_Q2_2008

,max(CASE WHEN Calendar_year = 2007 AND Calendar_quarter = 2 THEN ORDERS ELSE '' END) as Rank_Q2_2007

FROM

(

select *,RANK() over (PARTITION BY Calendar_year,Calendar_quarter ORDER BY TOTALCOST DESC) as ORDERS from #temp11

) p

GROUP BY TEST_NAME

http://unitech.vn

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

Nuts, u made us (rather, me) 70 % clear 😀

Now, on what basis should we rank the results? TotalCost ??

Like, the test that cost the highest in a particular quarter for a particular year will be Rank Number 1 ?

Pls make us a bit more clear on your desired result based on the input data u have given mate.

Cheers!!

Nuts SSCrazy Points: 2547 More actions · Answer 9

Yes thats right.

I need to rank it on totalcost

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

I think nguyennd has got the code you wanted...

Cheers!!

WayneS SSC Guru Points: 95460 More actions · Answer 11

COldCoffee (4/8/2010)
I think nguyennd has got the code you wanted...
Cheers!!

I agree. Good job.

Keep in mind the difference between RANK() and DENSE_RANK() if it is possible to have a tie.

Nuts, do you see how things work better when you supply code that shows the problem? A lot of people that wouldn't bother with your problem suddenly jump in and help you out. All because they can cut/paste your code and be testing out solutions immediately.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

WayneS (4/9/2010)
Nuts, do you see how things work better when you supply code that shows the problem? A lot of people that wouldn't bother with your problem suddenly jump in and help you out. All because they can cut/paste your code and be testing out solutions immediately.

Superbly put Wayne!! 🙂

Cheers!!!

Paul White SSC Guru Points: 150467 More actions · Answer 13

WayneS (4/9/2010)
Nuts, do you see how things work better when you supply code that shows the problem? A lot of people that wouldn't bother with your problem...

I was one of those people! 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nuts SSCrazy Points: 2547 More actions · Answer 14

Hey Thanks a lot guys. Just tested the query and it runs fine! 🙂

And thanks to everyone for pointing me to the right direction of posting the ddls for my queries.

I will keep this in mind for my future posts... 🙂 🙂

cheers:-D