Ranking by aggregating two fields

Transact-SQL
1

Hello all,

I have a dataset which shows records with a year and a country field. Within each year, I want to be able to rank each country to show the top X countries for each year. I then want to use this data to show the movement up and down of countries each year.

I can't think of a way to automate this using SQL and wondered if anyone had any ideas!

The script is very long which is why I haven't included here.

Thanks in advance

2

Usually one would use RANK or DENSE_RANK functions in SQL Server to calculate rankings.

A related function - ROW_NUMBER - might also be useful/preferable in some cases.