March 6, 2015 at 12:52 pm
i have a table containing the top 5 teams points for the current season using the rank function, and as well as their points for last season.
the only problem is that i want to add a sequential ranking to the table so that if there are 2 or more teams with the same score this season, they are ranked based on their points last season;
League -- Team - Season -- Points
League 1 AAA 2013-14 90
League 1 BBB 2013-14 80
League 1 CCC 2013-14 75
League 1 DDD 2013-14 70
League 1 EEE 2013-14 65
League 1 AAA 2014-15 90
League 1 BBB 2014-15 80
League 1 CCC 2014-15 80
League 1 DDD 2014-15 70
League 1 EEE 2014-15 65
Any suggestions?
Cheers
March 6, 2015 at 1:41 pm
bharatgi (3/6/2015)
i have a table containing the top 5 teams points for the current season using the rank function, and as well as their points for last season.the only problem is that i want to add a sequential ranking to the table so that if there are 2 or more teams with the same score this season, they are ranked based on their points last season;
League -- Team - Season -- Points
League 1 AAA 2013-14 90
League 1 BBB 2013-14 80
League 1 CCC 2013-14 75
League 1 DDD 2013-14 70
League 1 EEE 2013-14 65
League 1 AAA 2014-15 90
League 1 BBB 2014-15 80
League 1 CCC 2014-15 80
League 1 DDD 2014-15 70
League 1 EEE 2014-15 65
Any suggestions?
Cheers
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2015 at 3:29 pm
Thanks Sean.
Apologies for using the wrong format, I wasn't sure which ifcode to use, but will remember for future reference
March 6, 2015 at 3:35 pm
bharatgi (3/6/2015)
Thanks Sean.Apologies for using the wrong format, I wasn't sure which ifcode to use, but will remember for future reference
Glad that worked for you. 🙂
The point really wasn't about formatting, it was about the content. When you post tables and data the people who help can simply copy and paste and start working on the problem right away as opposed to writing all the inserts and such.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply