0

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.

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • what is 10? what is 2? what is 9? why 10? why 2? why 9? – Giorgi Nakeuri Jun 16 '15 at 14:19
  • please see this answer http://stackoverflow.com/questions/7747327/sql-rank-versus-row-number – TheGameiswar Jun 16 '15 at 14:21
  • @GiorgiNakeuri 10 means that within the Property "RAV" and "UK" market, Smith is ranked number 10 in February 2015, based on his sales amount. Same logic for the rest. – user3115933 Jun 16 '15 at 15:53
  • @TheGameiswar Thanks for the link but I am still a little confused about rank and row number. By rank, I meant "given a rank number from largest to smallest". In my example, if I have 20 records for UK market for Property X for February 2015, it will assign Rank 1 to the agency having achieved the highest Sales and so on, with the agency achieving the lowest sale given Rank 20. – user3115933 Jun 16 '15 at 15:59

1 Answers1

2

Yes, you can write something like this:

SELECT *, RANK() OVER(PARTITION BY Agency, mht ORDER BY sales DESC)
FROM [yourTable]
Ionic
  • 3,884
  • 1
  • 12
  • 33
  • By the way. If you want a rank with distinctive values you can use dense_rank() which will work the same as my code example above. – Ionic Jun 16 '15 at 12:38
  • May be I was not explicit enough in my post. I want the ranking to be done based on the Sales amount. I have edited my post accordingly. – user3115933 Jun 16 '15 at 14:05
  • I've changed the script, just change the column and change the sort order. Should do the same as the script before. – Ionic Jun 16 '15 at 14:16
  • @Ionic Thanks but that's not exactly what I wanted. In my example, if I have 20 records for UK market for Property X for February 2015, it will assign Rank 1 to the agency having achieved the highest Sales and so on, with the agency achieving the lowest sale given Rank 20. Basically, the query needs first to group all data within the same property, same month, and same market and then rank from there, giving the agency with the highest sales RANK 1 and so on. So, my 45,000 records will be grouped based on Property, month and market and ranking will be done within these clusters. – user3115933 Jun 16 '15 at 16:04
  • I've added the grouping - which was originally present as you said it was wrong :-D. I don't know which fields were needed for your grouping. But you should get it with this example. – Ionic Jun 16 '15 at 19:44