Results 1 to 2 of 2

Thread: Ranking

  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Ranking

    Hi list,

    Wondering if you could help me out here? I've got a table like this:

    id name data
    1 sully 567
    2 sully 789
    3 sully 456
    4 denan 890
    5 denan 678

    now I want to have this:

    id name data rank
    1 sully 567 2
    2 sully 789 1
    3 sully 456 3
    4 denan 890 1
    5 denan 678 2

    I'm think along the lines with count, something like this:

    select id,name,data, (select count (names) from table as tmp where data.tmp > data.table) as rank

    This (I know) is not correct. What would be?

    Greetings sjors

  2. #2
    Join Date
    Sep 2003
    Posts
    19

    Re: Ranking

    set db = currentdb
    set rst = db.openrecordset("select unique name from tablename")
    do until rst.eof
    inti = 1
    set rst2 = ("select * from tablename where name = """ & rst!name & """" & "order by data desc")
    do until rst2.eof
    rst.edit
    rst!rank = inti
    rst.update
    inti = inti + 1
    rst2.movenext
    loop
    rst.movenext
    loop




    Originally posted by Coekie
    Hi list,

    Wondering if you could help me out here? I've got a table like this:

    id name data
    1 sully 567
    2 sully 789
    3 sully 456
    4 denan 890
    5 denan 678

    now I want to have this:

    id name data rank
    1 sully 567 2
    2 sully 789 1
    3 sully 456 3
    4 denan 890 1
    5 denan 678 2

    I'm think along the lines with count, something like this:

    select id,name,data, (select count (names) from table as tmp where data.tmp > data.table) as rank

    This (I know) is not correct. What would be?

    Greetings sjors

Posting Permissions

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