MS access sql rank query

[CRX2C]

Hi Everyone,

I'm trying to create a rank query from access to rank the lowest cost as shown below. Below are all the fields in my table and I am ranking them based on ORG STATE and DEST PROV. The example below is two transportation carriers going to two different origin/destination (CA to AB and CA to ON). I need to rank the lowest cost based on these pairings. I would appreciate if someone can provide me the sql query for this. Thanks in advance

Carrier Name	ORG STATE	DEST PROV	# of shipments	Total Cost	Rank
Carrier A	CA	AB	293	$50,000	1
Carrier B	CA	AB	293	$75,000	2
Carrier A	CA	ON	150	$42,000	2
Carrier B	CA	ON	150	$40,000	1

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[xenou]

Some sites give these answers:
https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql
https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/

I'm not sure how they deal with ties. The second URL doesn't explain, but it seems to ignore ties. You could first query for distinct values, then use the given approach, to treat ties equally. The first URL I didn't look at closely enough.

Edit: I guess it goes without saying you can also push/pull your data into an excel workbook and use Excel's rank function as well.

 

[CRX2C]

Some sites give these answers:
https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql
https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/

I'm not sure how they deal with ties. The second URL doesn't explain, but it seems to ignore ties. You could first query for distinct values, then use the given approach, to treat ties equally. The first URL I didn't look at closely enough.

Edit: I guess it goes without saying you can also push/pull your data into an excel workbook and use Excel's rank function as well.

Thanks. I tried those examples but somehow I keep getting a syntax error.

 

[xenou]

With your provided data, this was the adaptation of the second url's strategy:

SELECT T1.[ORG STATE], T1.[DEST PROV], T1.[Total Cost],  
       (SELECT COUNT(T1.[ORG STATE])
          FROM
                 [MyTable] T2
         WHERE 
			T2.[ORG STATE] = T1.[ORG STATE]
			AND T2.[DEST PROV] = T1.[DEST PROV]
			AND T2.[Total Cost] <= T1.[Total Cost]) AS Rank
FROM 
	[MyTable] T1
ORDER BY [ORG STATE], [DEST PROV], [Total Cost]

If we want to count ties equally, one way is to get rid of duplicates first, so:
Define Query1 first:

SELECT DISTINCT [ORG STATE], [DEST PROV], [Total Cost] FROM MyTable;

Then using Query1:

SELECT T1.[ORG STATE], T1.[DEST PROV], T1.[Total Cost],  
       (SELECT COUNT(T1.[ORG STATE])
          FROM
                 [Query1] T2
         WHERE 
			T2.[ORG STATE] = T1.[ORG STATE]
			AND T2.[DEST PROV] = T1.[DEST PROV]
			AND T2.[Total Cost] <= T1.[Total Cost]) AS Rank
FROM 
	[Query1] T1
ORDER BY [ORG STATE], [DEST PROV], [Total Cost]