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,
JimRunning 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|||Jim,
if you are using SS 2005 , use row_number() or rank() OLAP function|||Where do you want to show data?
If you use front end application, do Ranking there
Madhivanan
jim_geissman@.countrywide.com wrote:
Quote:
Originally Posted by
> 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
No comments:
Post a Comment