Ranking Query

[grnegsndhm]

I would like the have a query where a persons rank amonst a group is returned by the evaluation a column.

Name Sales Rank
Jill 500 2
Bill 501 1
John 399 3

Ideas?

 

[sfreeman@co.mer]

Please POST your desired recordset... what do you want to see result from the query...

 

[grnegsndhm]

Need to rank the person by the sales column and show their ranking as a number not just ordering by:

Input Record:
Person Sales
Sally 500
Fred 400
John 600

Output of Query
Person Rank
Sally 2
Fred 3
John 1

 

[sfreeman@co.mer]

Try this...

Two queries.

First:

SELECT
tblSales.Name
, Sum(tblSales.Sales)

AS
SumOfSales

FROM
tblSales

GROUP BY
tblSales.Name

ORDER BY
Sum(tblSales.Sales) DESC;

Second:

SELECT
qrySales.Name
, qrySales.SumOfSales
, (SELECT Count(*) FROM qrySales t1

WHERE
t1.SumofSales >= qrySales.SumOfSales) AS Rank

FROM
qrySales;

Resulting Data shoud look something like:

Name SumOfSales Rank
Ronald $7,900.00 1
Tommy $5,000.00 2
John $4,500.00 3
Robert $2,900.00 4
Sally $2,200.00 5
Jane $1,800.00 6
Bob $1,200.00 7
Jim $1,000.00 8
Peter $700.00 9
Tim $350.00 10
Ralph $150.00 11

HTH