• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

How to select second cheapest, third cheapest, etc. Access 2007

Hello, do do I pick a specific price point in Access 2007? For example, we are in the long distance telephone industry have have a pool of carriers that service area codes. I would like a query that says "show the third cheapest price for Area Code 562.

In this case the result would be: 562,PBELL,0.09
 
I'm only familiar with the First and Max expressions, so I can only see the cheapest and the most expecnsive... I am trying to see the second cheapest or the fifth cheapest etc.
|AreaCode|Carrier|Price|
|562     |ATT    |0.05 |
|562     |VZN    |0.03 |
|562     |SPRNT  |0.15 |
|562     |PBELL  |0.09 |
|562     |MJCK   |0.11 |

Open in new window

0
McFoxx
Asked:
McFoxx
  • 3
  • 3
  • 2
  • +3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This example will give the 3rd one at the top of the list:

SELECT TOP 3 Table1.FIELD1
FROM Table1
ORDER BY Table1.FIELD1 DESC;

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
sorry ignore that.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... if say you use the TOP property like this:

SELECT TOP 3 Table1.FIELD4
FROM Table1
ORDER BY Table1.FIELD4;
this will give you the lowest 3.  You could save this query ... say qryLowThree.

Then this query - based on that query will give you the 3rd lowest value:

SELECT Max(qryLowThree.FIELD4) AS [3rd Lowest]
FROM qryLowThree;

Unfortunately,  you can't dynamically input the TOP number with a prompt.  You would  to use vba code and execute the SQL ... where in you could supply the TOP parameter.

mx
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
McFoxxAuthor Commented:
Doesn't seem to be working for me... I will be more specific in the examples below.

I am trying to select the third cheapest rates for each NPA-NXX combo, so that I may export them into a spreadsheet.

After I have that data I would modify the query to return the fourth cheapest rates per NPA-NXX combo and export that data into a separate spreadsheet, etc.

I am trying to present 5 separate documents that show "these are our least expensive rates per NPA-NXX, these are our second lease expensive rates per NPA-NXX, these are our third cheapest rates per NPA-NXX, these are our fourth cheapest rates per NPA-NXX, etc"

The database is quite large, with about 2.4GB worth of these records loaded.
What table "master_rate_records" looks like(and it's field names):
+---+---+-------+------+
|NPA|NXX|Carrier|Rate  |
----+---+-------+------+
|201|007|EPMB   |0.0098|
|201|007|FICC   |0.0275|
|201|007|DRTC   |0.0250|
|201|007|FPSD   |0.0072|
|201|007|VVMI   |0.0086|
|201|032|GKNR   |0.0219|
|201|032|ECOV   |0.0190|
|201|032|EPMB   |0.0125|
|201|032|XEOV   |0.0131|
|318|934|YWGU   |0.0061|
|318|934|GKNR   |0.0057|
|318|934|VVIM   |0.0087|
|318|934|KNLB   |0.0073|
+---+---+-------+------+

Desired query result in Access if searching for "third cheapest per NPA-NXX":
+---+---+-------+------+
|NPA|NXX|Carrier|Rate  |
----+---+-------+------+
|201|007|EPMB   |0.0098|
|201|032|ECOV   |0.0190|
|318|934|KNLB   |0.0073|
+---+---+-------+------+

Open in new window

0
 
GRayLCommented:
SELECT a.AreaCode, a.Carrier, a.Price (SELECT Count(b.Price) FROM myTable b WHERE b.AreaCode=a.AreaCode AND b.Carrier = a.Carrier and b.Price <=A.Price) as PriceCountAsc FROM myTable a Order By a.AreaCode, a.Carrier, a.Price;

When you get a feel for how this query works then you can add conditions which limit which area code and which price count.
0
 
chwong67Commented:
Try:
SELECT *,  ( SELECT COUNT(*) + 1  FROM table1 AS T2   WHERE T1.NPA=T2.NPA AND T1.NXX=T2.NXX and T1.RATE  > T2.RATE ) AS rank
FROM TABLE1 AS T1
WHERE (SELECT COUNT(*) + 1  FROM table1 AS T2   WHERE T1.NPA=T2.NPA AND T1.NXX=T2.NXX and T1.RATE  > T2.RATE) = 3
ORDER BY NPA, NXX, RATE
0
 
chwong67Commented:
Or
SELECT *,  ( SELECT COUNT(*)   FROM table1 AS T2   WHERE T2.NPA=T1.NPA AND T2.NXX=T1.NXX and T2.RATE  <= T1.RATE ) AS rank
FROM TABLE1 AS T1
WHERE (SELECT COUNT(*)   FROM table1 AS T2   WHERE T2.NPA=T1.NPA AND T2.NXX=T1.NXX and T2.RATE  <= T1.RATE) <= 3
ORDER BY NPA, NXX, RATE
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
GRayLCommented:
With the table  PhoneRates as shown in the snippet and running this query:

SELECT a.NPA, a.NXX, a.Carrier, a.Rate, (SELECT Count(b.Rate) FROM PhoneRates b WHERE b.NPA = a.NPA and b.Rate <= a.Rate) AS Ranking
FROM PhoneRates  a WHERE (SELECT Count(b.Rate) FROM PhoneRates b WHERE b.NPA = a.NPA and b.Rate <= a.Rate) = 3 ORDER BY 1;

which I developed from my post at http:#a35013912  it returned this result when 3 was entered after the prompt:

NPA      NXX      Carrier      Rate      Ranking
201      007      EPMB      0.0098      3
318      934      KNLB      0.0073      3

I think this is the solution that was asked for.

NPA	NXX	Carrier	Rate
201	007	EPMB	0.0098
201	007	FICC	0.0275
201	007	DRTC	0.025
201	007	FPSD	0.0072
201	007	VVMI	0.0086
201	032	GKNR	0.0219
201	032	ECOV	0.019
201	032	EPMB	0.0125
201	032	XEOV	0.0131
318	934	YWGU	0.0061
318	934	GKNR	0.0057
318	934	VVIM	0.0087
318	934	KNLB	0.0073

Open in new window

0
 
South ModModeratorCommented:
All,
 
Following an 'Objection' by GRayL (at http://www.experts-exchange.com/Q_27108230.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
 
At this point I am going to re-start the auto-close procedure.
 
Thank you,
 
SouthMod
Community Support Moderator
0
 
GRayLCommented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now