Results 1 to 12 of 12
  1. #1
    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.
    Program, that's what I do.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you looking for row numbers or running totals? Either (or both) can be done. I assume you are order by COL1 Descending?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    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.
    Program, that's what I do.

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

  5. #5
    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.
    Program, that's what I do.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    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.
    Program, that's what I do.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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!

    Program, that's what I do.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It'll get you the results you are after. It is a pretty standard solution to your class of problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    here
    Posts
    19

    Thumbs up

    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.
    Program, that's what I do.

Posting Permissions

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