sql - MS ACESS rank columns by multiple criteria

Question

I'm trying to create a query with rank values.

I Have a Table with somwthing like this

Ref  lenght  qty order
A     1000   2   order1
A     1200   1   order2
B     2000   1   order3
C     500    1   order4

The Rank siould be

Ref  lenght  qty order  rank
A     1000   2   order1  2
A     1200   1   order2  2
B     2000   1   order3  1
C     1100    1   order4 3

for My rank only the bigguest value of an order sould be analysed.

I'm really new at acess but need this for work.

Any help

question from:https://stackoverflow.com/questions/65920241/ms-acess-rank-columns-by-multiple-criteria

Answer 1

Presumably, you want the ranking based on length. This would be simpler in almost any other database, but in MS Access, you can use:

select t.*, o2.ranking
from (select o.*,
             (select count(*)
              from (select max(length) as max_length
                    from order as o2
                    group by o2.order
                   ) as o2
              where o2.max_length >= o.max_length
             ) as ranking
      from (select order, max(length) as max_length
            from t
            group by order
           ) as o
     ) as o2 join
     t
     on o2.order = t.order;

Note: I can only hope that this handles ties in the maximum length the way you want them handled. Modifying that logic would be quite tricky.

By comparison, in any reasonable database you would use:

select t.*,
       dense_rank() over (order by max_length desc) as ranking
from (select t.*,
             max(length) over (partition by order) as max_length
      from t
     ) t;

To me, this seems like a good reason to switch databases.