There are a number of ways this could be done. In the example I wrote I first split the seasons into their own groups using a cte, then a third cte to rank the teams. You could streamline this but I split it apart to demonstrate the logic that needs to happen. Then all you have to do is your final select and use the ordering established previously.

Please notice how I posted ddl and data in a readily consumable format. This is something you should do in the future so the volunteers around here don't have to spend their time doing that first.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

League varchar(15)

, Team varchar(5)

, Season varchar(10)

, Points int

)

insert #Something

select 'League1', 'AAA', '2013-14', 90 union all

select 'League1', 'BBB', '2013-14', 80 union all

select 'League1', 'CCC', '2013-14', 95 union all

select 'League1', 'DDD', '2013-14', 70 union all

select 'League1', 'EEE', '2013-14', 65 union all

select 'League1', 'AAA', '2014-15', 90 union all

select 'League1', 'BBB', '2014-15', 80 union all

select 'League1', 'CCC', '2014-15', 80 union all

select 'League1', 'DDD', '2014-15', 70 union all

select 'League1', 'EEE', '2014-15', 65

;

with PreviousSeason as

(

select *, RANK() over (order by Points desc) as MyRank

from #Something

where Season = '2013-14'

)

, CurrentSeason as

(

select *, RANK() over (order by Points desc) as MyRank

from #Something

where Season = '2014-15'

)

, RankedTeams as

(

select c.League

, c.Team

, c.MyRank as CurrentRank

, p.MyRank as PreviousRank

from CurrentSeason c

join PreviousSeason p on c.Team = p.Team

)

select s.*

from #Something s

join RankedTeams rt on rt.Team = s.Team

order by Season desc, CurrentRank, PreviousRank