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...@xxxxxxxxxxxxxxx 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

.

Relevant Pages

  • Re: Getting at @@IDENTITY
    ... int ID //identity column ... char 10 fld1 ... Now I need the identity column from that insert. ...
    (microsoft.public.sqlserver.ce)
  • Re: Few queries
    ... the type of identity column which can be tinyint, smallint, int, bigint, ... You get error message if the identity value greater than ... > C] Can we have DML statements in the body of function? ...
    (microsoft.public.sqlserver.programming)
  • Re: Is this an MSSQL bug?
    ... > I have the following query on MSSQL 2000. ... > declare @t table(i int identity, j int, k int) ... > An explicit value for the identity column in table '@t' can only be ...
    (microsoft.public.sqlserver.programming)
  • RE: ASP.NET and @@IDENTITY
    ... INSERT INTO SomeTable (IntValue, SomeTextValue) ... SqlConnection conn = new SqlConnection; ... //Assumes an int type for the IDENTITY column ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Choice of Primary Key/Identity Fields
    ... > That means it's not guaranteed unique (an identity does not guarantee ... > colref_id int, ... > Even if this is the case not having a unique index on an identity column ...
    (microsoft.public.sqlserver.programming)