How to get rank?

[jim_geissman@countrywide.com]

I would like to write a query that gives me the values of a set of
observations, and their rank.

CREATE TABLE #Values(val int)
INSERT #Values SELECT 1
INSERT #Values SELECT 5
INSERT #Values SELECT 10

I would like to select this:

1 10 -- rank 1, value 10
2 5
3 1

I can put them into a temp table with an identity column, ordered by
the column I'm interested in, and then retrieve in order by the
identity column. I'm wondering if there's a way to do that with a
subquery.

Thanks,
Jim

[SQL Menace]

Re: How to get rank?

Running count is one way

CREATE TABLE #Values(val int)
INSERT #Values SELECT 1
INSERT #Values SELECT 5
INSERT #Values SELECT 10

select (select count(*) from #Values v where val <= v2.Val) as Rank,*
from #Values v2
order by 2

Denis the SQL Menace

http://sqlservercode.blogspot.com/

jim_geiss...@co untrywide.com wrote:[color=blue]
> I would like to write a query that gives me the values of a set of
> observations, and their rank.
>
> CREATE TABLE #Values(val int)
> INSERT #Values SELECT 1
> INSERT #Values SELECT 5
> INSERT #Values SELECT 10
>
> I would like to select this:
>
> 1 10 -- rank 1, value 10
> 2 5
> 3 1
>
> I can put them into a temp table with an identity column, ordered by
> the column I'm interested in, and then retrieve in order by the
> identity column. I'm wondering if there's a way to do that with a
> subquery.
>
> Thanks,
> Jim[/color]

[Alexander Kuznetsov]

Re: How to get rank?

Jim,

if you are using SS 2005 , use row_number() or rank() OLAP function

[Madhivanan]

Re: How to get rank?

Where do you want to show data?
If you use front end application, do Ranking there

Madhivanan


jim_geissman@co untrywide.com wrote:

> I would like to write a query that gives me the values of a set of
> observations, and their rank.
>
> CREATE TABLE #Values(val int)
> INSERT #Values SELECT 1
> INSERT #Values SELECT 5
> INSERT #Values SELECT 10
>
> I would like to select this:
>
> 1 10 -- rank 1, value 10
> 2 5
> 3 1
>
> I can put them into a temp table with an identity column, ordered by
> the column I'm interested in, and then retrieve in order by the
> identity column. I'm wondering if there's a way to do that with a
> subquery.
>
> Thanks,
> Jim