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

Question

I am trying to write an SQL Query on a table in MSAccess to add a virtual Column that will add sequential Letters of the Alphabet based on a Value column sorted in Descending order.

------------------------------------------------
|    Filename    |    Zone    |    ValueCol    |
------------------------------------------------
|    abc         | Zone_MEA   |      33        |
|    abc         | Zone_DEA   |      29        |
|    abc         | Zone_SEO   |      21        |
|    abc         | Zone_GUY   |      09        |
|-----------------------------------------------
|    def         | Zone_SEO   |      30        |
|    def         | Zone_DEA   |      22        |
|    def         | Zone_MEA   |      07        |
|    def         | Zone_GUY   |      06        |
|----------------------------------------------|
|    ghi         | Zone_GUY   |      21        |
|    ghi         | Zone_MEA   |      12        |
|    ghi         | Zone_SEO   |      04        |
|    ghi         | Zone_DEA   |      04        |
------------------------------------------------

So all values in ValueCol sorted in descending order will receive a sequential letter starting from A per Zone set.

                                                  Virtual Col
---------------------------------------------------------------
|    Filename    |    Zone    |    ValueCol    |    Letter    |
---------------------------------------------------------------
|    abc         | Zone_MEA   |      33        |       A      |
|    abc         | Zone_DEA   |      29        |       B      |
|    abc         | Zone_SEO   |      21        |       C      |
|    abc         | Zone_GUY   |      09        |       D      |
|-------------------------------------------------------------|
|    def         | Zone_SEO   |      30        |       A      |
|    def         | Zone_DEA   |      22        |       B      |
|    def         | Zone_MEA   |      07        |       C      |
|    def         | Zone_GUY   |      06        |       D      |
|-------------------------------------------------------------|
|    ghi         | Zone_GUY   |      21        |       A      |
|    ghi         | Zone_MEA   |      12        |       B      |
|    ghi         | Zone_SEO   |      04        |       C      |
|    ghi         | Zone_DEA   |      04        |       D      |
---------------------------------------------------------------

Is there a way to write such an SQL query in MSAccess without resorting to creating any physical helper tables? (Exception maybe a virtual helper table, but don't know how to create one or how it may be used.)

EDIT: Each section is one particular filename.

Wrote this query on suggestions from @Erik A. Here's the query:

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

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            S.[FILENAME] = M.[FILENAME]
             AND S.[ZONE] <= M.[ZONE]
            AND S.[VALUECOL] <= M.[VALUECOL]
            AND S.[FILENAME]&S.[ZONE]&S.[VALUECOL]<=M.[FILENAME]&M.[ZONE]&M.[VALUECOL]
    ) ) AS POS

FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC

The Alphabetical order is still not sequential as can be seen in the below output.
Also getting duplicate letters within a particular FILENAME section.

Edit...once again: This takes of point 2 i.e. Duplicates, but not point 1.

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

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            S.[FILENAME] = M.[FILENAME]
            AND S.[FILENAME]&S.[ZONE] <= M.[FILENAME]&M.[ZONE]
             AND S.[FILENAME]&S.[ZONE]&S.[VALUECOL]<=M.[FILENAME]&M.[ZONE]&M.[VALUECOL]
    ) ) AS POS

FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC

SQL table represent *unordered* sets. A table has no ordering unless you specify the ordering. How are the groups defined? — Gordon Linoff, Feb 23 '20 at 18:46
What does your table look like? Is it the first demo data bit? Does the filename change each time, or is each section one particular file name/? — Nathan_Sav, Feb 23 '20 at 18:50
@Nathan_Sav my bad! yes you are right. each section is one particular filename. — sifar, Feb 23 '20 at 19:18

score 4 · Accepted Answer · edited Feb 24 '20 at 09:21

4

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

edited Feb 24 '20 at 09:21

sifar

1,086
1
17
43

answered Feb 23 '20 at 19:08

Erik A

31,639
12
42
67

i have edited the table. One section is each particular filename. Trying to understand your query and see if it can be applied to my above table. Why are you comparing 2 different columns viz., `s.GroupingColumn1 = m.GroupingColumnN`? – sifar Feb 23 '20 at 19:34
How can your above query be applied to suit the edited table? – sifar Feb 23 '20 at 19:35
I'm just demonstrating, in general, that you can apply this to multiple grouping columns, as you didn't state the current grouping columns. You still haven't specified how you're ordering these, so I can't apply it to your specific situation – Erik A Feb 23 '20 at 21:14
the grouping column will be `Filename`. The `Zone` position can change depending on how they get ranked (in descending order) based on Value column. Though the Zones will be the same for each filename section. The Letters will then be applied in sequential order i.e. A for the top highest value, B for the 2nd highest, C for the 3rd highest and so on... – sifar Feb 23 '20 at 22:06
ok. can the ties be broken by concatenating 2 or more columns and comparing? something like e.g. `s.filename&s.zone&s.valuecol=m.filename&m.zone&m.valuecol` – sifar Feb 24 '20 at 06:40
Just provide a well-defined `ORDER BY` statement. While you can do it by concatenating, just ordering by multiple columns is more logical and efficient. Note that the ordering column uses `<=`, not `=`. – Erik A Feb 24 '20 at 07:06
Understood. BTW what does this line mean? `s.GroupingColumn1 = m.GroupingColumnN`? are they 2 different columns of tables s and m? – sifar Feb 24 '20 at 07:23
Oh, sorry, that's a typo, you should just match all grouping columns – Erik A Feb 24 '20 at 07:25
Ah! i was so confused by this line. – sifar Feb 24 '20 at 07:26
I've edited my answer as well. Concatenation doesn't work with different sort orders, of course – Erik A Feb 24 '20 at 08:47
Wow! this is Excellent! @Erik A, Can you briefly explain the logic for the AND and OR used? On closer look you seem to be defining the sections somehow. But still not clear to me. – sifar Feb 24 '20 at 09:19
1

It's replicating sorting: Sort by A, unless A is identical, then sort by B within this A. You can easily expand this by adding sorting conditions. This also allows you to change `<` and `>`, which are equivalent to `ASC` and `DESC` in the `ORDER BY` statement. Unfortunately, it gets very lengthy and deeply nested very quickly if you have many sorting conditions, luckily you only had 2 relevant ones. – Erik A Feb 24 '20 at 09:25
Just a last question @Erik A. If the `ValueCol` is a derived or calculated column using another query (i.e. not a part of original table columns), then this results in `Your query does not include the specified expression '' as a part of an aggregate function`. If i choose any other original table column, it works, but not with a calculated column. – sifar Feb 24 '20 at 09:59
For deriving off other queries, this should work, but Access's optimizer can get confused and break it anyway. If you provide a [mre], I might be able to track the bug down, it can usually be solved by adding silly things to your `GROUP BY` clause. As for calculated columns, well, don't do calculated columns. See [Allen Browne's site](http://allenbrowne.com/casu-14.html) for why. – Erik A Feb 24 '20 at 10:03
Here is the [https://drive.google.com/file/d/1gLy4xRyOGOtjab-w_c7lQ22xCDONqWKq/view?usp=sharing](SampleDB). See the issue with the `qry_Sample2` where the `Letters` are to be assigned sequentially on the descending order of `[% OF ALL APPROVED]`. Eventually, i would be expanding the `qry_Sample1` query SQL in `qry_Sample2` in place of their names. – sifar Feb 24 '20 at 14:02

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

1 Answers1