Rank over problem - 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 over problem

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-24 : 15:22:48

Hi all.
I have a problem with rank over statement.

I have a simple table.
col1 col2
a 1
b 58
...

I want to add the rank over col2 and select the rows with the rank equal to 7.
Here is my query
----------
SELECT a
,b
,rank() over (order by col2) as RankNum
from mytable
where RankNum=7
----------

But SQL can not recognize the new column "RankNum".
I do not want to put it into a temporary table and then select from that table. Is there anyway to let SQL recognize the new column "RankNum"?

Thanks

8022421
Starting Member

45 Posts

Posted - 2009-02-24 : 15:33:24

Try Like this...
SELECT tb.a,
tb.b,
tb1.RankNum
FROM mytable tb,
( SELECT a
,b
,rank() over (order by col2) as RankNum
from mytable) tb1
where RankNum=7
AND tb.a = tb1.a

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-24 : 15:35:40

That is basically similar as putting into a temporary table. But thank you anyway.

quote:
Originally posted by 8022421

Try Like this...
SELECT tb.a,
tb.b,
tb1.RankNum
FROM mytable tb,
( SELECT a
,b
,rank() over (order by col2) as RankNum
from mytable) tb1
where RankNum=7
AND tb.a = tb1.a

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-24 : 15:36:00

May be this...

SELECT col1,
col2
FROM (SELECT *,rank() over (order by col2) as RankNum
from mytable) a
where a.RankNum=7

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-24 : 15:39:55

Oops..dint see your reply above..must have posted at the same time...never mind.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 15:57:11

quote:
Originally posted by mavershang

Hi all.
I have a problem with rank over statement.

I have a simple table.
col1 col2
a 1
b 58
...

I want to add the rank over col2 and select the rows with the rank equal to 7.
Here is my query
----------
SELECT a
,b
,rank() over (order by col2) as RankNum
from mytable
where RankNum=7
----------

But SQL can not recognize the new column "RankNum".
I do not want to put it into a temporary table and then select from that table. Is there anyway to let SQL recognize the new column "RankNum"?

Thanks

what is you Ranking based on?

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-24 : 15:58:04

Thanks for your reply.

quote:
Originally posted by vijayisonly

Oops..dint see your reply above..must have posted at the same time...never mind.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-02-24 : 15:58:41

The rank is based on the value of Col2.

quote:
Originally posted by sodeep

quote:
Originally posted by mavershang

Hi all.
I have a problem with rank over statement.

I have a simple table.
col1 col2
a 1
b 58
...

I want to add the rank over col2 and select the rows with the rank equal to 7.
Here is my query
----------
SELECT a
,b
,rank() over (order by col2) as RankNum
from mytable
where RankNum=7
----------

But SQL can not recognize the new column "RankNum".
I do not want to put it into a temporary table and then select from that table. Is there anyway to let SQL recognize the new column "RankNum"?

Thanks

what is you Ranking based on?