Results 1 to 2 of 2

Thread: Ranking In MS Access

  1. #1
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13

    Lightbulb Ranking In MS Access

    Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
    In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

    However same result can be achieved using a correlated query.

    e.g. Consider a dataset of student marks below.

    Code:
    Students	    Marks
    Tony	              34
    Bob	              32
    Thor	              48
    Jack	              42
    Tom	              41
    Kate	              45
    Sid	              26
    Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

    PHP Code:
    SELECT Students,
           (
    SELECT COUNT(T1.Marks
              
    FROM
                     
    [Table] AS T1 
             WHERE T1
    .Marks >= T2.Marks) AS Rank 
    FROM 
          
    [Table] AS T2 
    ORDER BY Marks DESC 
    Last edited by DocAElstein; 10-06-2023 at 08:51 PM.
    Regards,

    Transformer

  2. #2
    Junior Member
    Join Date
    Sep 2023
    Posts
    1
    Rep Power
    0
    If you are ranking about and wanking with data and need to break wind or break a tie, you can do something like this which uses another variable to "break the tie" when there are more than one "Marks" with the same count.

    Code:
    SELECT T2.Students,
           (SELECT COUNT(T1.Marks)
            FROM [Table] AS T1
            WHERE T1.Marks > T2.Marks OR (T1.Marks = T2.Marks AND T1.ID < T2.ID)) + 1 AS Rank
    FROM [Table] AS T2
    ORDER BY T2.Marks DESC, T2.ID;
    This YouTube video also shows how to do it:






    https://www.youtube.com/@alanelston2330/featured
    Last edited by DocAElstein; 10-06-2023 at 08:51 PM. Reason: wanking and farting about

Similar Threads

  1. Full Outer Join in MS Access
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-18-2013, 08:42 PM
  2. Access Query Help
    By Vipergs8v10 in forum Access Help
    Replies: 2
    Last Post: 05-08-2013, 06:32 PM
  3. Access spreadsheet specifies the log
    By marreco in forum Excel Help
    Replies: 3
    Last Post: 02-07-2013, 04:13 AM
  4. Replies: 1
    Last Post: 04-06-2012, 07:43 PM
  5. help with after insert event in Access 10
    By richlyn in forum Access Help
    Replies: 9
    Last Post: 03-03-2012, 10:49 PM

Tags for this Thread

Posting Permissions

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