Rank Function

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Does anyone know if there is a way to rank a record based
on a field. Similar to the rank function in excel where
if there are 10 records and I want to rank them on Sales
it will assign the rank of 1 to the highest etc.
Preferrably I would like to have this functionality
within a query and not having to 'write' the rank to a
table.

Any suggestions appreciated!
 
Dear Dan:

A correlated subquery is used for ranking in a query.

SELECT *, (SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Sales > T.Sales)
FROM YourTable T
ORDER BY Sales

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