Assigning a Rank 1

[OU18]

All,

How can I write a query to assign a rank of 1,2 or 3 to a table.

What I currently have is data like the example below

Route ID Price Vendor Rank
1 .005 X
1 .006 A
1 .007 C
1 .009 D

There are multiple Route IDs. What I need to do is assign and 1 to lowest price, 2 to the second lowest, etc by Route ID. So at each route id change it would rank 1,2,3 again. My table is currently in the proper order. Sorted by Route ID and then by Price. Yet we are loading this to a switch, so I need to identify the 3 lowest rates. This isnt the last step, just one in many that will based on the rank field.

How can I complete this. Any help is appreciated.

 

[Golom]

Try this

Select A.RouteID, A.Price, A.Vendor,

       (Select Count(*) From tbl As B
        Where  B.RouteID = A.RouteID AND
               B.Price <= A.Price ) As Rank

From tbl As A

Warning: This is a correlated subquery and its probably going to be slow on a large table. You may want to make it a MakeTable query and save the results in another table of you need to do processing on it with subsequent queries.

 

[OU18]

Golom,

I modified what you sent me to the following

SELECT (Select count(*) from [tblLataRate-List] Where [Route ID Lata]=[t].[Route ID LATA] and [ID]< [t].[ID])+1 AS Seniority, t.Vendor, t.[ROUTE ID LATA], t.RATE
FROM [tblLATARATE-List] AS t;

And it works great with one small exception. When i try to make it a make table query, it basically takes forever for it to generate.

There are some 140,000 records in the table. What I am looking for is to identify the top 3 based on rate starting with the smallest and going to the highest. I need to do this, so I can put the top three into the following columns

1st choice, 2nd choice, 3rd choice. Yet it takes forever to complete this.

Any suggestions.

thanks for your help thus far.

 

[Golom]

As I said ... its a correlated subquery and the inner query is run for each of the 140,000 records in the outer query. If you need to get results for EVERY record then about all you can do is make sure that you have indexes built on the [Route ID Lata] and [ID] fields, then sit back with a really big cup of [insert beverage of choice here] while it runs.

The other option is to consider the possibility that you don't really need to know the answer for all possible instances of [Vendor] and/or [Route ID Lata]. Inserting a WHERE clause to restrict the retrieval to specific vendors or Routes will probably drastically reduce the run time. Since I don't know what your business problem is, I can't really say if that's a realistic suggestion.