Query For Second Highest Number - To include additional Fields

  • Thread starter Thread starter rl_davis
  • Start date Start date
R

rl_davis

I have a table called tblRates. I need to find out the second highest
pricing discount into a state for each state out of four different
states. The table fields are:

Carrier_Name
Origin_State
Dest_State
Carrier_Discount

So the data looks like this:

Carrier_Name Origin_State Dest_State Carrier_Discount
Roadway TN AL 62
Yellow TN AL 53
ABF TN AL 57
SAIA TN AL 59
Roadway TN AR 61
ABF TN AR 57

What I need is the following showing the carrier with the second
highest discount into the state:

Carrier_Name Origin_State Dest_State Carrier_Discount
ABF TN AL 57
ABF TN AR 57

I can get the highest discount using Select
max(tblRates.[Carrier_Discount]) but can not get the second highest.

I have experimented with querying the table against the table, but can
not figure out.

I have tried Select Distinct Top 2
.[field] but can not then
group information to include carrier name, origin state, or destination
state. I only get the top two discounts for the entire table.

Any assistance would be greatly appreciated. Thank you in advance.

Robert
 
There may be a better way but this is what I'd do:

Make a query selecting only the Top 1 (i.e. max) of your discount rates.
Then use that query and your original table in a "Find Unmatched Query" (use
the wizard if you need to) and grab the max of what that query returns.

Hope that helps
 
I finally got the answer to the second highest value in a table.

I first queried for the max amount of the discount, then I queried for
the max amount of the discount where the discount from the table was
not equal to the max discount of the first query.

Below is the first query:

SELECT DISTINCT tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State], Max(tblLatestVersionOfRates.Discount) AS MaxOfDiscount

FROM tblLatestVersionOfRates

GROUP BY tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State]

HAVING (((tblLatestVersionOfRates.[Origin City])="ashland city"));

The following is the second query (SECOND HIGHEST NUMBER):

SELECT tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State], Max(tblLatestVersionOfRates.Discount) AS MaxOfDiscount1

FROM tblLatestVersionOfRates INNER JOIN qryMaxDiscountByLane ON
(tblLatestVersionOfRates.Discount <>
qryMaxDiscountByLane.MaxOfDiscount) AND (tblLatestVersionOfRates.[Dest
State] = qryMaxDiscountByLane.[Dest State]) AND
(tblLatestVersionOfRates.[Origin State] = qryMaxDiscountByLane.[Origin
State]) AND (tblLatestVersionOfRates.[Origin City] =
qryMaxDiscountByLane.[Origin City])

WHERE (((tblLatestVersionOfRates.[Origin City])="Ashland City"))

GROUP BY tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State];

The following is a query that combines the first query with the
original table to give me the information that I need as output. This
is the maximum discount for the carrier by state originating from and
destined to:


SELECT tblLatestVersionOfRates.[Origin City],
qryMaxDiscountByLane.[Origin City], tblLatestVersionOfRates.[Origin
State], qryMaxDiscountByLane.[Origin State],
tblLatestVersionOfRates.[Dest State], qryMaxDiscountByLane.[Dest
State], tblLatestVersionOfRates.Discount,
qryMaxDiscountByLane.MaxOfDiscount, tblLatestVersionOfRates.SCAC
FROM tblLatestVersionOfRates INNER JOIN qryMaxDiscountByLane ON
(tblLatestVersionOfRates.Discount = qryMaxDiscountByLane.MaxOfDiscount)
AND (tblLatestVersionOfRates.[Dest State] = qryMaxDiscountByLane.[Dest
State]) AND (tblLatestVersionOfRates.[Origin State] =
qryMaxDiscountByLane.[Origin State]) AND
(tblLatestVersionOfRates.[Origin City] = qryMaxDiscountByLane.[Origin
City]);

Now for the SECOND HIGHEST Discount with the fields I wanted to group.

SELECT tblLatestVersionOfRates.[Origin City],
qrySecondHighestLTLRates.[Origin City], tblLatestVersionOfRates.[Origin
State], qrySecondHighestLTLRates.[Origin State],
tblLatestVersionOfRates.[Dest State], qrySecondHighestLTLRates.[Dest
State], tblLatestVersionOfRates.Discount,
qrySecondHighestLTLRates.MaxOfDiscount1, tblLatestVersionOfRates.SCAC

FROM tblLatestVersionOfRates INNER JOIN qrySecondHighestLTLRates ON
(tblLatestVersionOfRates.Discount =
qrySecondHighestLTLRates.MaxOfDiscount1) AND
(tblLatestVersionOfRates.[Dest State] = qrySecondHighestLTLRates.[Dest
State]) AND (tblLatestVersionOfRates.[Origin State] =
qrySecondHighestLTLRates.[Origin State]) AND
(tblLatestVersionOfRates.[Origin City] =
qrySecondHighestLTLRates.[Origin City])

ORDER BY tblLatestVersionOfRates.[Dest State];

Thank you very much for your help. I wanted to post in case it could
help someone else.

Robert said:
There may be a better way but this is what I'd do:

Make a query selecting only the Top 1 (i.e. max) of your discount rates.
Then use that query and your original table in a "Find Unmatched Query" (use
the wizard if you need to) and grab the max of what that query returns.

Hope that helps


I have a table called tblRates. I need to find out the second highest
pricing discount into a state for each state out of four different
states. The table fields are:

Carrier_Name
Origin_State
Dest_State
Carrier_Discount

So the data looks like this:

Carrier_Name Origin_State Dest_State Carrier_Discount
Roadway TN AL 62
Yellow TN AL 53
ABF TN AL 57
SAIA TN AL 59
Roadway TN AR 61
ABF TN AR 57

What I need is the following showing the carrier with the second
highest discount into the state:

Carrier_Name Origin_State Dest_State Carrier_Discount
ABF TN AL 57
ABF TN AR 57

I can get the highest discount using Select
max(tblRates.[Carrier_Discount]) but can not get the second highest.

I have experimented with querying the table against the table, but can
not figure out.

I have tried Select Distinct Top 2
.[field] but can not then
group information to include carrier name, origin state, or destination
state. I only get the top two discounts for the entire table.

Any assistance would be greatly appreciated. Thank you in advance.

Robert
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top