Choosing bottom 3 for multiple entries

[Astello]

I have a database with information on carriers, lanes, and prices.
I need to choose the cheapest 3 carriers for each lane.
The lanes are either 3-digit zips to 3-digit zips or state to state
(410-300 or CA to GA, etc.)
So for each of these lanes, I need to find the 3 cheapest carriers all
in one query.
So the resulting table should look like:

Lane Carrier Price
...........
TN-FL Schneider $50
TN-FL JB Hunt $66
TN-FL Dart $78
646-303 JB Hunt $77
etc.....

Any ideas on how to do this? Thanks in advance!

 

[KARL DEWEY]

Try this ---

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price))<=3))
ORDER BY T.Lane, T.Price;

 

[Astello]

Marvelous! Thanks so much.

Try this ---

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price))<=3))
ORDER BY T.Lane, T.Price;

I have a database with information on carriers, lanes, and prices.
I need to choose the cheapest 3 carriers for each lane.
The lanes are either 3-digit zips to 3-digit zips or state to state
(410-300 or CA to GA, etc.)
So for each of these lanes, I need to find the 3 cheapest carriers all
in one query.
So the resulting table should look like:

Lane Carrier Price
...........
TN-FL Schneider $50
TN-FL JB Hunt $66
TN-FL Dart $78
646-303 JB Hunt $77
etc.....

Any ideas on how to do this? Thanks in advance!

 

[Astello]

I'm having trouble coming up with the top 3 entries now. The method on
the microsoft website doesn't work for me. Any suggestions?
(Basically your code, but for top 3 instead of botton 3)

Marvelous! Thanks so much.

Try this ---

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price))<=3))
ORDER BY T.Lane, T.Price;

I have a database with information on carriers, lanes, and prices.
I need to choose the cheapest 3 carriers for each lane.
The lanes are either 3-digit zips to 3-digit zips or state to state
(410-300 or CA to GA, etc.)
So for each of these lanes, I need to find the 3 cheapest carriers all
in one query.
So the resulting table should look like:

Lane Carrier Price
...........
TN-FL Schneider $50
TN-FL JB Hunt $66
TN-FL Dart $78
646-303 JB Hunt $77
etc.....

Any ideas on how to do this? Thanks in advance!

 

[KARL DEWEY]

Note the >= vs <= and the DESC as order.

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price >= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price >= T.Price))<=3))
ORDER BY T.Lane, T.Price DESC;

I'm having trouble coming up with the top 3 entries now. The method on
the microsoft website doesn't work for me. Any suggestions?
(Basically your code, but for top 3 instead of botton 3)

Marvelous! Thanks so much.

Try this ---

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price))<=3))
ORDER BY T.Lane, T.Price;


:

I have a database with information on carriers, lanes, and prices.
I need to choose the cheapest 3 carriers for each lane.
The lanes are either 3-digit zips to 3-digit zips or state to state
(410-300 or CA to GA, etc.)
So for each of these lanes, I need to find the 3 cheapest carriers all
in one query.
So the resulting table should look like:

Lane Carrier Price
...........
TN-FL Schneider $50
TN-FL JB Hunt $66
TN-FL Dart $78
646-303 JB Hunt $77
etc.....

Any ideas on how to do this? Thanks in advance!

 

[Astello]

Awesome, thanks so much. I didn't think about changing the order.

Note the >= vs <= and the DESC as order.

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price >= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price >= T.Price))<=3))
ORDER BY T.Lane, T.Price DESC;

I'm having trouble coming up with the top 3 entries now. The method on
the microsoft website doesn't work for me. Any suggestions?
(Basically your code, but for top 3 instead of botton 3)

Marvelous! Thanks so much.

KARL DEWEY wrote:
Try this ---

SELECT T.Lane, T.Carrier, T.Price, (SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price) AS Rank
FROM Astello AS T
WHERE ((((SELECT COUNT(*)
FROM [Astello] T1
WHERE T1.Lane = T.Lane
AND T1.Price <= T.Price))<=3))
ORDER BY T.Lane, T.Price;


:

I have a database with information on carriers, lanes, and prices.
I need to choose the cheapest 3 carriers for each lane.
The lanes are either 3-digit zips to 3-digit zips or state to state
(410-300 or CA to GA, etc.)
So for each of these lanes, I need to find the 3 cheapest carriers all
in one query.
So the resulting table should look like:

Lane Carrier Price
...........
TN-FL Schneider $50
TN-FL JB Hunt $66
TN-FL Dart $78
646-303 JB Hunt $77
etc.....

Any ideas on how to do this? Thanks in advance!