Ranking in query

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a query of our top customers but would like to add
a field called rank to my query starting with 1 and going
to whatever our lowest ranked customer would be. How
would I do this?

Thanks,

Chuck
 
You can use a subquery to do this - here's a simple example which includes a
rownumber for a list of customers which are sorted by CustName and using
CustName as the ranking field. Hopefully you can adapt this to your
situation. Just change the field in the criteria of the subquery and in the
orderby of the main query to use the field you are using for ranking. Note
that the subquery can only return one field, in this case it's a count. Also
note that this is not going to be very efficient if your query returns more
than a couple of thousand rows (maybe less if the rest of the query is very
complex).

SELECT
(select
count(*)
from
customers as c2
where
c2.custname < c1.custname) +1 as RowNumber,
c1.Custname,
c1.customerid
FROM
Customers as c1
ORDER BY
by c1.custname;
 
Back
Top