Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: rank records in access sql (row number)

    Hi,

    I want to have a column which shows the rank in a column based on an existing column.
    ex: col 2 is a number column having values 1..10
    I want a query which will rank these records based on their descending order..i.e record having col2 value = 10 will have rank 1, value=9 will have rank 2 etc...

    if 2 column have the same value, they will have the same rank.

    can anyone help me out? I am an access newbie..

    in sql server , we have rownumber() function

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mustang123
    in sql server , we have rownumber() function
    You'd use RANK() or DENSE_RANK() in SQL Server for this. These work differently, so you'd also need to explain what behaviour you would want for your purposes.

    There is no equivalent in Access I'm afraid. I suspect a version of RANK() or DENSE_RANK() will be an ugly and innefficient VBA function. If there are only a handful of rows it might not be too bad though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You can do a similar thing in Access using a query.
    SELECT final_rankings.tipper_name, final_rankings.total, (select count(*) from test_rankings where [total]>[final_rankings]![total]+1 AS Rankings
    FROM test_rankings AS final_rankings
    ORDER BY final_rankings.total DESC;

    test_rankings is the name of the table
    final_rankings is the table's alias in the query.
    final_rankings.total is the total field

    This will not display an = sign when two rankings are the same

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm. Good solution. It works like RANK(). Not sure how to do DENSE_RANK().
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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