|
|
Posted by SQL Menace on 10/02/08 11:51
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...@countrywide.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
Navigation:
[Reply to this message]
|