Ranking query

[Leslie Isaacs]

Hello All

I have two queries that work OK. They each calculate a particular value
([basic_rate] in the first query, and [basic_time] in the second) for each
[client] in table [tbl_clients] (using certain other tables to do so).

It would be very useful if the two queries could also output each client's
ranking of [basic_rate] and [basic_time] respectively - so the client with
the highest [basic_rate] gets a ranking of 1, the next highest gets 2, etc.,
and the same for the other query that calculates [basic_time].

Other than using make-table queries with an autonumber field, I cannot see
how to do this.

Hope someone can help.

Many thanks
Leslie Isaacs

 

[Guest]

Here is an outline for adding a ranking column to query results that you may
be able to utilise

Example: TableA has two columns (col1, col2) that are to be selected with a
third calculated column that has 1 for the row with the highest value of
col2, 2 for the next highest value etc.

The SQL would be
SELECT T1.col1, T1.col2, 1+Sum(IIF(T1.col2 = T2.col2,0,1)) AS rank
FROM TableA AS T1 INNER JOIN TableA AS T2 ON T1.col2 <= T2.col2
GROUP BY T1.col1, T1.col2
ORDER BY T1.col2 DESC

If you knew that each value of col2 was unique, you could replace the
1+Sum(iif statement with a simpler Count(T2.col2)

Hope This Helps
Gerald Stanley MCSD

 

[Tom Ellison]

Dear Leslie:

The general solution is to create an aliased instance of the table in
a subquery that COUNT()s the number of rows with a "better" basic_rate
or basic_time from within the existing query's results. Since the
best one will have 0 rows that are better, and you want this to rank
as 1, you must add one to that count.

Perhaps that would look like:

SELECT client, basic_rate,
(SELECT COUNT(*) + 1 FROM QueryA Q1
WHERE Q1.basic_rate > Q.BasicRate) AS Rank
FROM QueryA Q
ORDER BY basic_rate

Use the actual name of your query where this says QueryA and check my
usage of the column names.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts