Professional Web Applications Themes

rank w/ reset on group by - Microsoft SQL / MS SQL Server

looking for anything but a cursor solution. temp tables, identity columns, OK. DDL: create table t1 (colA int, colB char(1)) DML: insert t1 values (1,'A') insert t1 values (1,'B') insert t1 values (1,'C') insert t1 values (2,'A') insert t1 values (2,'D') insert t1 values (3,'A') insert t1 values (3,'E') insert t1 values (3,'F') --Desired query result: COLA COLB Rank ----- ---- ---- 1 A 1 1 B 2 1 C 3 2 A 1 2 D 2 3 A 1 3 E 2 3 F 3...

  1. #1

    Default rank w/ reset on group by

    looking for anything but a cursor solution. temp tables, identity columns,
    OK.

    DDL:
    create table t1 (colA int, colB char(1))

    DML:
    insert t1 values (1,'A')
    insert t1 values (1,'B')
    insert t1 values (1,'C')
    insert t1 values (2,'A')
    insert t1 values (2,'D')
    insert t1 values (3,'A')
    insert t1 values (3,'E')
    insert t1 values (3,'F')

    --Desired query result:

    COLA COLB Rank
    ----- ---- ----
    1 A 1
    1 B 2
    1 C 3
    2 A 1
    2 D 2
    3 A 1
    3 E 2
    3 F 3




    Kevin Guest

  2. #2

    Default Re: rank w/ reset on group by

    Can be one solution ...
    Select a.colA, a.colB, (Select count(*) from t1 b where b.colA=a.colA and
    b.ColB<=a.ColB) [RunningCount] from t1 a

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    www.extremeexperts.com


    "Kevin" <only> wrote in message
    news:phx.gbl... 
    columns, 


    Vinodk Guest

  3. #3

    Default Re: rank w/ reset on group by

    Try,

    select a.colA, a.colB, rank = count(*)
    from tableA as a inner join tableA as b
    on a.colA = b.colA and a.colB >= b.colB
    group by a.colA, a.colB
    order by a.colA, a.colB



    AMB


    Alejandro Guest

  4. #4

    Default Re: rank w/ reset on group by

    Excellent work, smithers!

    .....thanks...


    "SriSamp" <co.in> wrote in message
    news:uslY$phx.gbl... 
    > columns, 
    >
    >[/ref]


    Kevin Guest

Similar Threads

  1. Page Rank Function
    By dadress2 in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: October 23rd, 01:30 PM
  2. getting a user rank from database?
    By davellaman in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: July 25th, 06:17 AM
  3. Generating a Rank column
    By Neil in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 09:39 PM
  4. How to I RANK my list?
    By Vishal Parkar in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 10th, 07:55 PM

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