Rank in sql ? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Rank in sql ?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-17 : 06:18:55

I have table :
Result1 Rank
5
6
78
4
27
3

How to rank in above table ?
Thank you very much !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 06:31:50

[code]SELECT t.Result1,
(SELECT COUNT(*) FROM Table w WHERE w.Result1 <= t.Result1) Rank
FROM Table t[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 06:34:16

Or, if you prefer descending ranking

SELECT	t.Result1,
	(SELECT COUNT(*) FROM Table w WHERE w.Result1 >= t.Result1) Rank
FROM	Table t

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 06:37:06

If the same result is duplicated then, the method wont work

Check this.


Declare @Tbl Table
(
Result int
)

Insert @Tbl
Select 5 Union All
Select 6 Union All
Select 78 Union All
Select 4 Union All
Select 27 Union All
Select 3 Union All
Select 3 Union All
Select 27 

SELECT	t.Result,
	(SELECT COUNT(*) FROM @Tbl w WHERE w.Result <= t.Result) Rank
FROM	@Tbl t
order by 1

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 06:40:34

Will this do?

select	t.result1,
	1 + (select count(*) from @table w where w.result1 < t.result1) Rank
from	@table t

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 06:45:22

well output is comming like this

The Same problem, there is no one with the Rank 2 ??

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 07:10:58

Should it? Two are claiming rank 1 and next value should be 3, right?
As most sport events do.

Happy with this one?

select	t.result1,
	 1+(select count(distinct result1) from @table w where w.result1 < t.result1) Rank
from	@table t
order by 1, 2

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 07:12:01

quote:
Originally posted by chiragkhabaria

well output is comming like this
3 1
3 1
4 3
5 4
6 5
27 6
27 6
~~78 8~~
78 7

Where do the second 78 come from?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 07:13:20

Yeah i m happy now lets see the orginal poster is happy or not ??

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-17 : 11:32:47

If original poster wants to show data in front end application, Rank should be done there

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-17 : 12:27:28

Are you running 2005? SQL 2005 provides a function to calculate ranks for you.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-18 : 03:33:02

quote:
Originally posted by jsmith8858

Are you running 2005? SQL 2005 provides a function to calculate ranks for you.

- Jeff

Better than that: it provides 3!
ROW_NUMBER() (break ties arbitrarily)
RANK() (rank ties together with a gap after)
DENSE_RANK() (rank ties together with no gaps)

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-21 : 00:30:46

Thank all !
I want to display the form :
3 1
3 1
4 2
5 3
6 4
27 5
27 5
78 6
78 6
I use SQL Server 2000 !
Thank you very much !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-21 : 00:36:47

quote:
Originally posted by pamyral_279

Thank all !
I want to display the form :
3 1
3 1
4 2
5 3
6 4
27 5
27 5
78 6
78 6
I use SQL Server 2000 !
Thank you very much !

Use Peso's query

quote:
Originally posted by Peso

Should it? Two are claiming rank 1 and next value should be 3, right?
As most sport events do.

Happy with this one?
select t.result1,
1+(select count(distinct result1) from @table w where w.result1 < t.result1) Rank
from @table t
order by 1, 2

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources