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.

enter image description here

Please advise me SQL code for MS-ACCESS.



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
    CurrentNumber = 1 + CurrentNumber
End If
CustomRank = CurrentNumber
End Function

Modify your query.

  1. 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.

  1. 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.

  2. 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;

enter image description here Output: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.