Use Access SQL to Do a Grouped Ranking

Use Access SQL to do a grouped ranking

SELECT *, (select count(*) from tbl as tbl2 where
tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl

Just add the dept field to the subquery...

Assign Rank/Index to Records in Sorted MS Access Query

Yes, you can use a query to achieve this result, by using a subquery:

SELECT Field1, Field2, Field3, Field4, Field5, 
(
SELECT Count(s.Field6)
FROM MyTable s
WHERE s.Field6 <= t.Field6
) As Rank
FROM MyTable t
ORDER BY Field6

Note that this will have a substantial impact on performance, as the subquery needs to be reran for every row.

Also note that for rows with an equal value for Field6, their rank will be equal, in contrast to your VBA code where their rank is not equal.

Ms Access Rank with two variables

You can either do this using a JOIN and GROUP BY:

SELECT  T.Class, 
        T.ChipNo, 
        T.Course, 
        T.Time, 
        COUNT(T2.ChipNo) + 1 AS [Rank]
FROM    T
        LEFT JOIN T T2
            ON T2.Class = T.Class
            AND T2.Course = T.Course
            AND T2.Time < T.Time
GROUP BY T.Class, T.ChipNo, T.Course, T.Time
ORDER BY T.CLass, T.Course, T.Time;

Or using a correlated subquery:

SELECT  T.Class, 
        T.ChipNo, 
        T.Course, 
        T.Time, 
        (   SELECT  COUNT(T2.ChipNo) + 1 
            FROM    T T2
            WHERE   T2.Class = T.Class
            AND     T2.Course = T.Course
            AND     T2.Time < T.Time
        ) AS [Rank]
FROM    T
ORDER BY T.CLass, T.Course, T.Time;

I have not tested these in access, but as far as I can remember I have not used any unsupported syntax.

Working Examples on SQL Fiddle

Examples are using SQL Server as closest comparable DBMS on SQL-Fiddle. In addition, I have left your original rank in these examples for comparison

SQL Query in MSAccess to Rank a Value Column with Letters based on it's Sort Order

A very general solution for a very general question:

If you have well-defined ordering (you order by a column that doesn't have duplicates) and grouping, you can use a subquery to achieve this:

It would look like this:

SELECT 
    (
        SELECT COUNT(*)
        From MyTable s
        WHERE 
            s.GroupingColumn1 = m.GroupingColumn1
            AND s.GroupingColumnN = m.GroupingColumnN
            AND s.SortingColumn1 <= m.SortingColumn1
    )
FROM MyTable m
GROUP BY GroupingColumn1, GroupingColumnN
ORDER BY SortingColumnN

That gets you the position of the items within the groups.

You can easily convert this to capital letters using a little knowledge of the ASCII table (A = position 65, capitals are all sequential, so by incrementing the position by 64 and looking up the ASCII character for the position, you'll get A for 1, B for 2, etc)

Chr(MyPosition + 64)

Of course, if the table is stored in a backend that supports window functions, this can be done more clearly, concisely, and faster. Unfortunately, Access does not support them.

Ordering should be implemented using > and <, which makes the statement fairly long for multiple ordering conditions:

SELECT M.[FILENAME], M.[ZONE],M.[VALUECOL],

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            (S.[FILENAME] = M.[FILENAME])
            AND (
                           (s.VALUECOL > m.VALUECOL)
                          OR (
                                    (s.VALUECOL = m.VALUECOL) AND (s.ZONE <= m.ZONE)
                                )
                       )
            ) ) AS LETTER
FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC

---

Related Topics