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

1. Registered User
Join Date
Jul 2003
Location
here
Posts
19

## Unanswered: 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.

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Are you looking for row numbers or running totals? Either (or both) can be done. I assume you are order by COL1 Descending?

3. Registered User
Join Date
Jul 2003
Location
here
Posts
19
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.

4. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
What does the desired outpout look like? It looks like you are after
Code:
```select count(*), sum(col2), sum(col3)
from yourtable```

5. Registered User
Join Date
Jul 2003
Location
here
Posts
19
the desired output would be something like the following:

HTML Code:
```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.

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
This is a general solution that numbers the rows of your dataset in descending order of Col0:

Code:
```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```

7. Registered User
Join Date
Jul 2003
Location
here
Posts
19
Code:
```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...
Last edited by tOeJaM; 01-11-05 at 18:00.

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

9. Registered User
Join Date
Jul 2003
Location
here
Posts
19
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!

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

11. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
It'll get you the results you are after. It is a pretty standard solution to your class of problem.

12. Registered User
Join Date
Jul 2003
Location
here
Posts
19
r937 -

DevShed

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
Last edited by tOeJaM; 01-13-05 at 17:18.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•