get the rank of returned rows (was "SQL Question")

Given the following results:
col0 col1 col2
THY 2,265,850 31
VIE 1,474,994 20
RID 1,221,800 17
ACC 1,124,335 15
FEI 445,184 6
DIR 433,783 6
ROM 324,365 4

What is the best way in a query to get the rank of the returned rows by either col1 or col2. In other words who's the number 1,2,3 etc...

total count col0 = 7
total col1 = 7,290,310
total col2 (would eqaul 100%)= 99%

Looking for a mathmatical solution to this any help would be appreciated.Are you looking for row numbers or running totals? Either (or both) can be done. I assume you are order by COL1 Descending?|||Actaully either but it must be based on the totals. I'm playing with a sub-query at the moment trying to use INTENDTITY(INT,1,1) as myRanK field. Only problem is I don't have control of the resulting inner query. I guess I could use a temp table to query against but I was trying to do this in as few trips as possible.|||What does the desired outpout look like? It looks like you are after

select count(*), sum(col2), sum(col3)
from yourtable|||the desired output would be something like the following:

col0 col1 col2 col3(aka Rank)
THY 2,265,850 31 1
VIE 1,474,994 20 2
RID 1,221,800 17 3
ACC 1,124,335 15 4
FEI 445,184 6 5
DIR 433,783 6 6
ROM 324,365 4 7

So I'd have a rank based on the sum of either col1 or col2 against the totals for the group.

Right now I'm trying something like the following but having trouble controlling my returned records from the inner query:

SELECT IDENTITY (INT, 1, 1) AS rank,q.*
FROM (SELECT col0,col1,col2 FROM mytable) q
ORDER BY q.col1

not working as I'd expect. :confused:|||This is a general solution that numbers the rows of your dataset in descending order of Col0:

Select YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2,
count(SecondInstance.col0) as Rank
from YourDataSet
inner join YourDataSet SecondInstance on YourDataSet.col1 <= SecondInstance.col1
group by YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2|||SELECT q.*,IDENTITY (INT, 1, 1) AS rank
INTO db.dbo.TEST
FROM (SELECT col0,col1,SUM(CASE WHEN Date >= '01/01/2004' AND Date <= '12/31/2004' THEN someValue ELSE 0 END)
AS col2
FROM tab1 INNER JOIN
tab2 ON tab1.ID = tab2.ID
WHERE (Date >= '01/01/2004') AND (Date <= '12/31/2004')
GROUP BY col0,col1) q
ORDER BY q.col1 DESC

Better example of what I'm working with...|||hey toejam, did you try blindman's suggestion with the theta join?

FYI you guys should read IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set (http://www.winnetmag.com/SQLServer/Article/ArticleID/43553/43553.html)|||r937,

I read blindmans post but I wasn't sure if it would get me the results I'm after but I'll try it.

the link you provided is very on point so I'm going to take a moment to read through it.

Thx!

:D|||see http://forums.devshed.com/t218290/s.html|||It'll get you the results you are after. It is a pretty standard solution to your class of problem.|||r937 -

DevShed (http://forums.devshed.com/showthread.php?p=944761#post944761)

Thx very much... I did it the old fashion way. I did a little VB code to get my answer but I'd prefer to do it in a query to let the db do the work. Awesome job man!

Thx a bunch