Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    21

    Unanswered: How could I assign rank.

    I have the below query

    select [FI NAME], round (sum(FIWORKING.GROSPFT), 0)as FIGROSS from from FIWORKING
    where FIWORKING.[FI NAME] IS NOT NULL
    group by FIWORKING.[FI NAME]
    order by sum (FIWORKING."GROSPFT") DESC

    Which returns below

    name1 65784
    name2 32586
    name3 37892

    based on this, I would like to be able to set a rank (aka 1 , 2 , 3, etc)
    for NAME1, NAME2, etc

    I would like to store this in table GROSPFTRANK

    That Table looks like

    name1 1
    name2 2

    I haven't been able to figure out the SQL to do this.

    Thanks for an help

    Chris

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can create a table with an identity column. Insert into that table using your query and the ranking will automatically be applied.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are several ways to skin this cat.

    One method is to make the second field in your destination table an incrementing identity column, and then just insert your ordered data into it.

    A second method would be to create a temporary table with an autoincrement column and load your data into it prior to storing it in your permanent table.

    Or you could use this sql statement, which runs two totals and then counts the number of records in the second set which are less than the value in the first set:

    Insert into GROSPFTRANK ([FI NAME], [RANK])
    select [FIWORKINGOUTER].[FI NAME], count([FIGROSS].[FI NAME])
    from
    (select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
    from FIWORKING
    where FIWORKING.[FI NAME] IS NOT NULL
    group by FIWORKING.[FI NAME]) FIWORKINGOUTER
    inner join
    (select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
    from FIWORKING
    where FIWORKING.[FI NAME] IS NOT NULL
    group by FIWORKING.[FI NAME]) FIWORKINGSUB
    on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)

    Note that this assigns two [FI NAME] values the same rank if they have the same summary value. If you want unique ranks based on, say, the alphabetical order of [FI NAME], join the two subqueries with this ON statement:

    on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)
    or (FIWORKINGOUTER.FIGROSS = FIWORKINGSUB.FIGROSS
    and [FIWORKINGOUTER].[FI NAME] < [FIWORKINGSUB].[FI NAME])

    blindman

Posting Permissions

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