Professional Web Applications Themes

Ranks - Microsoft SQL / MS SQL Server

Hi all, I have the following theoretical table. [ID] [Sector] [ReturnID] [Date] [Price] [Rank within sector] 1 001 55 02/03 1.12 2 001 55 03/03 4.234 3 001 55 04/03 4.23 4 001 55 05/03 9.32 5 001 55 06/03 5.33 6 001 58 02/03 9.22 7 001 58 03/03 9.43 8 001 58 04/03 7.324 9 001 58 05/03 2.234 10 001 58 06/03 5.23 11 001 72 02/03 3.23 12 001 72 03/03 4.62 13 001 72 04/03 10.11 14 001 72 05/03 12.54 15 001 72 06/03 12.52 16 001 24 02/03 4.23 17 001 24 03/03 ...

  1. #1

    Default Ranks

    Hi all,

    I have the following theoretical table.


    [ID] [Sector] [ReturnID] [Date] [Price] [Rank within sector]
    1 001 55 02/03 1.12
    2 001 55 03/03 4.234
    3 001 55 04/03 4.23
    4 001 55 05/03 9.32
    5 001 55 06/03 5.33
    6 001 58 02/03 9.22
    7 001 58 03/03 9.43
    8 001 58 04/03 7.324
    9 001 58 05/03 2.234
    10 001 58 06/03 5.23
    11 001 72 02/03 3.23
    12 001 72 03/03 4.62
    13 001 72 04/03 10.11
    14 001 72 05/03 12.54
    15 001 72 06/03 12.52
    16 001 24 02/03 4.23
    17 001 24 03/03 7.34
    18 001 24 04/03 2.78
    19 001 24 05/03 7.43
    20 001 24 06/03 12.64

    What I want to do is populate the [Rank within sector] column. So that I
    can say that ReturnID 24 was ranked 3rd on 04/03, ReturnID 72 was 1st etc
    etc. I need to be able to do this for a particular Date within a particular
    sector, my actual table will have many more sectors, returns and dates in.

    I know how to do this using a cursor, however, I know I overuse cursors.
    There must be a better way surely. Combining two copies of the same table
    maybe?

    Any thoughts very much appreciated!

    Thanks,

    Steve


    Steve Guest

  2. #2

    Default Re: Ranks

    How about :

    UPDATE tbl
    SET rank = (SELECT COUNT(DISTINCT t1.id)
    FROM tbl t1
    WHERE t1.Sector = tbl.Sector
    AND t1.Date = tbl.Date
    AND t1.Price >= tbl.Price)
    WHERE rank IS NULL ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Ranks

    Anith,

    That'll do nicely ;-)

    Thanks also to Narayana.....v. useful link!

    Cheers,

    Steve

    "Steve" <net> wrote in message
    news:phx.gbl... 
    sector] 
    particular 


    Steve Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139