Ms-access – Help me to RANK correctly in Access

ms accessrank

I have such a table:
I want to rank the people, as who has the more V1 and the less V2, V3, V4, V5 be on top.

Please advise me SQL code for MS-ACCESS.

Best Answer

Variant.

Create a module. Copy the below code into it:

Public Function CustomRank(Optional key) As Integer
Static CurrentNumber As Integer
If IsMissing(key) Then
    CurrentNumber = 0
Else
    CurrentNumber = 1 + CurrentNumber
End If
CustomRank = CurrentNumber
End Function

Modify your query.

Add a field CustomRank(some_field) AS Rank into output list.

some_field must be some NOT NULL field (the best way - primary key, if PK is composite then use any field from primary key expression). If LEFT JOIN is used then it must be a field from the most left table.

If GROUP BY is used in the query then use any NOT NULL field from GROUP BY expression.

Any NOT NULL expression from output list is safe too.

Add the condition OR (CustomRank() > -1) to your WHERE conditions if no GROUP BY is used, or to your HAVING conditions if GROUP BY is used.
Add proper ORDER BY.

PS. This solution is NOT safe for parallel queries execution.

In your particular case the query will be:

SELECT sourcetable.id, sourcetable.NAME, sourcetable.V1, sourcetable.V2, sourcetable.V3, sourcetable.V4, sourcetable.V8, CustomRank(sourcetable.id) AS Rank
FROM sourcetable
WHERE (CustomRank()>-1)
ORDER BY sourcetable.V1 DESC , sourcetable.V2, sourcetable.V3, sourcetable.V4, sourcetable.V8;

Output:

Related Solutions

MySQL – How to Get the Rank of a User in a Score Table

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

Mysql – How to rank the total score in order of positions

I hate giving complete solutions but here you go:

START TRANSACTION;
create table total_sc_tmp as (select student_id, SUM(total_score) tot_sc
FROM
    scores
GROUP BY student_id);
select student_id,  tot_sc,
FIND_IN_SET(
tot_sc
, (SELECT 
    GROUP_CONCAT(
    distinct tot_sc
        ORDER BY tot_sc DESC) AS score
FROM
    total_sc_tmp)) as rank from total_sc_tmp;
  COMMIT;

reorder, use @row and avoid the tmp table if you want. Happy Easter to all of you with constructive comments

Best Answer

Related Solutions

MySQL – How to Get the Rank of a User in a Score Table

Mysql – How to rank the total score in order of positions

Related Question