Union Queries and Top N

[OU18]

All,

Is it possible in a union query to only bring in the top 3 lowest rates per Route ID LATA. I have pasted my query below. There can be mulitple RATES per ROute ID LATA.

SELECT [tblLATARATE].[ROUTE ID LATA],[tblLATARATE].[3 DIGIT LATA],[tblLATARATE].[OCN], [QWEST ER] AS RATE, "Qwest ER" AS Vendor
FROM [tblLATARATE] Where [QWEST ER]<1;

UNION
SELECT [tblLATARATE].[ROUTE ID LATA],[tblLATARATE].[3 DIGIT LATA], [tblLATARATE].[OCN],[GLOBAL Er] AS RATE, "Global Er" AS Vendor
FROM [tblLATARATE] where [GLOBAL Er]<1;

UNION
SELECT [tblLATARATE].[ROUTE ID LATA],[tblLATARATE].[3 DIGIT LATA],[tblLATARATE].[OCN], [Ntera Er] AS RATE, "Ntera Er" AS Vendor
FROM [tblLATARATE] where [Ntera Er]<1;

UNION
SELECT [tblLATARATE].[ROUTE ID LATA], [tblLATARATE].[3 DIGIT LATA], [tblLATARATE].[OCN],[US LEC Er] AS RATE, "US LEC ER" AS Vendor
FROM [tblLATARATE] where [US LEC Er]<1;

Thanks for the help

 

[PaulBricker]

Select Top 3

Union Select Top 3

Union Select Top 3

Use these inplace of Select and Union Select in your queries. That should do it.

Paul

 

[PHV]

Say that your union query is saved as qryGetRates:
SELECT A.* FROM qryGetRates A
WHERE A.RATE In (SELECT TOP 3 B.RATE FROM qryGetRates B WHERE B.[ROUTE ID LATA]=A.[ROUTE ID LATA] ORDER BY 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OU18]

PaulBricker and PHV

thanks to both of you for the quick responses. I will give them a try. I appreciate the help

Steve

 

[OU18]

PHV,
I tried your query and it locks up my machine. It appears it is running, then the screen goes blank and locks up.

PaulBricker,
I have also tried your query. It runs very quickly, however it does not give me the data I am looking for

It appears to list the top 3 ROUTE ID LATAs. What I need is the top 3 Rates per Route ID LATA. There are some 3000 Unique ROUTE ID LATAs in my table, each with 10 unique Rates listed. What makes them Unique is the Vendor that is gaurenteeing the rate. Of those 10 Vendors and their Rates, I need to know what the Top 3 Rates per Route ID LATA are. The top 3 equaling the 3 lowest rates.

Do I need to change my union query to read differently.

Thanks
Steve

 

[OU18]

Also, Here is some history behind the union query.

My table is currently listed in Columns.

Qwest ER NTera ER Global ER US Lec ER

In the columns is a rate for each of these vendors. What I was initially needing to find was the top 3 Mininum Rates within the columns by Route ID LATA. It was suggested that I normalize the data and put it in One Column, listing all the Rates in a column, and having a vendor column and Route ID LATA column.

Hence the Union Query.

Hopefully this helps you understand what I am looking to do. If you need a data sample to see what we are dealing with, i would be happy to send a sample of it.

 

[PaulBricker]

Sorry, I was thinking you would ORDER BY the field you need the Top 3 in, but a Union Query won't allow you to ORDER BY more than one Select statement within the UNION so that won't do you any good anyway.

Paul

 

[PHV]

I tried your query and it locks up my machine
You may try to create a maketable query based on your union query and then execute my suggestion against this temporary table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244