Results 1 to 15 of 15
  1. #1
    Tajaldeen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2023
    Posts
    5

    Rank in microsoft access without skipping any number

    Hi Guys,
    I have a table with three fields: ID, Name, Mark
    i also have created the following Query which ranks students in the table based on Mark
    Code:
    SELECT T2.Name, 1+(SELECT COUNT(T1.Mark) 
              FROM
                     [Table] AS T1 
             WHERE T1.Mark >T2.Mark) AS Rank
    FROM [Table] AS T2
    ORDER BY T2.Mark DESC;
    the problem: if there is a duplicated mark in tow records the query will skip a number like this:
    John 50 1
    Mark 60 2
    Ali 60 2
    Pall 70 4
    i dont want number 3 to be skipped so the rank will be like this: 1,2,2,3
    is there any solution ?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Review http://allenbrowne.com/ranking.html

    I don't think the solution is simple.
    Last edited by June7; 01-24-2023 at 01:44 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    And what is the criteria for one of them getting the rank 2 and another the rank 3? You toss a coin?

    Edit: I assumed you wanted to distribute rank 2 - my bad! But having following ranks reduced will be even worse - it disrupts the whole meaning of ranking!

    Of-course you can do this in case when instead ranking persons, you will rank their results. I.e. result 50 has rank 1, result 60 has rank 2, etc. It means, you have to redesign your query, make it a saved one, and then join this saved query with your original table to link result rank with person's result/mark!

    You have a query e.g. qMarkRanking which returns fields Mark and MarkRank

    Then you create a query like
    Code:
    SELECT t.Name, t.Mark, mr.MarkRank
    FROM [Table] t INNER JOIN qMarkRanking mr ON mr.Mark = t.Mark
    ORDER BY ....

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Arvil, I think OP wants result like.
    1
    2
    2
    3
    4
    5
    The tied records get same rank and continue the sequence without gap for other records.

    SQLServer has a function for this DENSE_RANK. Unfortunately, Access does not.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Tajaldeen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2023
    Posts
    5
    Quote Originally Posted by June7 View Post
    Arvil, I think OP wants result like.
    1
    2
    2
    3
    4
    5
    The tied records get same rank and continue the sequence without gap for other records.

    SQLServer has a function for this DENSE_RANK. Unfortunately, Access does not.
    .... Exactly .....

  6. #6
    Tajaldeen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2023
    Posts
    5
    Quote Originally Posted by ArviLaanemets View Post
    And what is the criteria for one of them getting the rank 2 and another the rank 3? You toss a coin?

    Edit: I assumed you wanted to distribute rank 2 - my bad! But having following ranks reduced will be even worse - it disrupts the whole meaning of ranking!

    Of-course you can do this in case when instead ranking persons, you will rank their results. I.e. result 50 has rank 1, result 60 has rank 2, etc. It means, you have to redesign your query, make it a saved one, and then join this saved query with your original table to link result rank with person's result/mark!

    You have a query e.g. qMarkRanking which returns fields Mark and MarkRank

    Then you create a query like
    Code:
    SELECT t.Name, t.Mark, mr.MarkRank
    FROM [Table] t INNER JOIN qMarkRanking mr ON mr.Mark = t.Mark
    ORDER BY ....
    I'm already ranking Marks, not Students , can you please show me an example or maybe it is okay to attach my file here to work on it, if this is not againts the rules here, its very simple file i just want to get the idea how to dot it.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Crossposted at Rank sequentially without skipping any number | Access World Forums (access-programmers.co.uk)

    Please read this article about the etiquette of crossposting A message to forum cross posters - Excelguru

    I use a slightly different approach for rank order queries.
    See whether you can adapt any of the examples in my article on ranking data in queries: Rank Order In Queries (isladogs.co.uk)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I think this is what you want

    Code:
    SELECT A.PK, A.Mark, Count(B.Mark) AS CountOfMark
    FROM Table3 AS A, (SELECT DISTINCT Mark FROM table3)  AS B
    WHERE (((B.Mark)<=[a].[mark]))
    GROUP BY A.PK, A.Mark
    ORDER BY A.Mark;
    Table 3 has these values
    PK Mark
    1 20
    2 25
    3 30
    4 30
    5 45
    6 15

    and produces this result
    PK Mark CountOfMark
    6 15 1
    1 20 2
    2 25 3
    3 30 4
    4 30 4
    5 45 5

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    For info the OP now has an answer at AWF so not worth further replies here
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    It uses a subquery so don't think it is as efficient as my suggestion

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The author of the other solution almost always uses subqueries whether they are needed or not.
    Anyway yours also has a subquery ��
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need student names associated with marks. So, CJ, your solution is incomplete. Include Name in SELECT and GROUP BY?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    Not a sub query but an aliased table

    by all means include a name instead of a pk. I was just demonstrating a principle. The example data is incomplete- what if 2 students have the same name or appear more than once for some reason

  14. #14
    Tajaldeen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2023
    Posts
    5
    Thank you all for your replies, i found the solution in this code

    Code:
    SELECT   T2.Name,1+(      SELECT         COUNT(*)      FROM         (            SELECT DISTINCT               Mark            FROM [TABLE]) AS T1      WHERE         T1.Mark> T2.Mark) AS RankFROM   [Table] AS T2ORDER BY   T2.Mark DESC
    SELECT T2.Name,1+( SELECT COUNT(*) FROM ( SELECT DISTINCT Mark FROM [TABLE]) AS T1 WHERE T1.Mark> T2.Mark) AS RankFROM [Table] AS T2ORDER BY T2.Mark DESC

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    yes, we know

    Please note that cross posting is allowed but with the request you provide a link to the crossposts so responders don't waste their time. Isladogs has posted a link about the etiquette of cross posting. Strongly recommend you read it. If you choose to ignore the advice given you will find many responders will choose not to waste their time responding, confident you will get your answer elsewhere. It's up to you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-02-2022, 03:39 PM
  2. Replies: 2
    Last Post: 01-15-2018, 07:46 PM
  3. Replies: 13
    Last Post: 10-06-2015, 09:12 AM
  4. Replies: 2
    Last Post: 04-07-2015, 05:34 AM
  5. Replies: 4
    Last Post: 03-01-2015, 11:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums