MS Access: Order by calculated field (using alias)

ms-access, sql

Solution

Use a Derived table

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

Problem

In a select query I calculate a field using a nested select. I would like to order the results by the calculated field (rank), however Access does not recognize the field rank. (When I run the query, Access asks for the parameter value of rank.) ``` SELECT *, (select count(*) from tbl as tbl2 where tbl.customers > tbl2.customers and tbl.dept = tbl2.dept ) + 1 as rank FROM tbl ORDER BY rank ``` [The example query is taken from this post]

Original source

Related problems