How to get the correct ranking from a query

[dveer]

I have the following table with prices that I want to rank by lane:

Supplier Lane Price
Supplier 1 X1000 1850
Supplier 1 X2000 1150
Supplier 1 X3000 548
Supplier 1 X4000 1800
Supplier 1 X5000 3200
Supplier 2 X1000 2000
Supplier 2 X2000 1150
Supplier 2 X3000 3500
Supplier 2 X4000 4500
Supplier 2 X5000 1450

I have created the following query:

SELECT Quotations.Supplier, Quotations.Lane, Quotations.Price, (SELECT
Count(*) FROM Quotations AS X WHERE X.Lane = Quotations.Lane AND X.Price
<=Quotations.Price) AS rank
FROM Quotations
ORDER BY Quotations.Lane, Quotations.Price;

This results in:

Supplier Lane Price rank
Supplier 1 X1000 1850 1
Supplier 3 X1000 2000 3
Supplier 2 X1000 2000 3
Supplier 4 X1000 2400 4
Supplier 1 X2000 1150 2
Supplier 2 X2000 1150 2
Supplier 4 X2000 4800 3
Supplier 3 X2000 4950 4

I would like to see the results as 1-2-2-4 for lane X1000 and 1-1-3-4 for
lane X2000.

Who can give me advice?
Thanks!

 

[vanderghast]

SELECT a.Supplier, a.Lane, a.Price, 1+COUNT(b.price) AS rank
FROM quotations AS a LEFT JOIN quotations AS b
ON a.Lane = b.Lane AND a.Price < b.Price
GROUP BY a.Supplier, a.Lane, a.Price



should do.


Vanderghast, Access MVP

 

[dveer]

Thanks, now it works!

SELECT a.Supplier, a.Lane, a.Price, 1+COUNT(b.price) AS rank
FROM quotations AS a LEFT JOIN quotations AS b
ON a.Lane = b.Lane AND a.Price < b.Price
GROUP BY a.Supplier, a.Lane, a.Price



should do.


Vanderghast, Access MVP