T-Sql syntax to RANK a field based on several criteria

Question

I have an SQL query in SQL Server 2014 that outputs the following (extract shown, real output is around 45,000 records):

ResaID  Agency   Sales     MTH           Market     Property

 235   Smith     500    February 2015      UK         RAV

 451   John     1600    February 2015     France      PLN

 258   Alan      800    January 2015      UK          BLS

I need an SQL Query that will RANK the agency column based on the following criteria: MTH, Market and Property and give me the following output (fictitious ranking shown below):

ResaId   Rank

 235       10

 451       2

 258       9

I will then use a JOIN based on ResaID to join the "Rank output" with my initial query.

In simpler terms, the ranking of the Agency will need to be done after grouping MTH, Market and Property.

Can this be achieved using T-SQL syntax?

Edit: I want the ranking to be done based on the Sales amount.

Answer 1

Yes, you can write something like this:

SELECT *, RANK() OVER(PARTITION BY Agency, mht ORDER BY sales DESC)
FROM [yourTable]