Hur man kan använda rank() och row_number() i sql-server 2005

Jag såg en fråga på ett forum där de hade en tabell med sportresultat och vill hämta ut de tre spelare från varje lag som hade fått högst poäng. Detta har innan sql-server 2005 varit relativt komplicerat. Men med rankfunktionerna i sql-server 2005 är det enkelt.

create table #teamresult (team varchar(100), player varchar(100), points int)
insert into #teamresult values (‘team 1’, ‘player 1’, 1)
insert into #teamresult values (‘team 1’, ‘player 2’, 2)
insert into #teamresult values (‘team 1’, ‘player 3’, 3)
insert into #teamresult values (‘team 1’, ‘player 4’, 4)
insert into #teamresult values (‘team 1’, ‘player 5’, 5)
insert into #teamresult values (‘team 2’, ‘player 1’, 5)
insert into #teamresult values (‘team 2’, ‘player 2’, 4)
insert into #teamresult values (‘team 2’, ‘player 3’, 3)
insert into #teamresult values (‘team 2’, ‘player 4’, 3)
insert into #teamresult values (‘team 2’, ‘player 5’, 1)

select * from
(select *, RANK() over(partition by team order by points desc) as ‘rank’ from #teamresult) t
where t.rank < 4[/sourcecode] Resultat: [sourcecode lang="html"] team player points rank team 1 player 5 5 1 team 1 player 4 4 2 team 1 player 3 3 3 team 2 player 1 5 1 team 2 player 2 4 2 team 2 player 3 3 3 team 2 player 4 3 3 [/sourcecode] Som ni ser så är det player 3-5 från team 1 och player 1-4 från team 2 som har högst poäng. Notera alltså att det är fyra spelare från team 2, det beror på att två spelare fick samma poäng. Jag tycker att detta är rätt sätt att lösa problemet på, men vill man absolut ha 3 poster så får man hitta något annat som skiljer posterna åt. Om man inte hittar någonting och vill lämna det åt slumpen kan man byta ut RANK mot ROW_NUMBER(). Exempelvis [sourcecode lang="sql"]select * from (select *, ROW_NUMBER() over(partition by team order by points desc) as 'row_number' from #teamresult) t where t.row_number < 4[/sourcecode]

2 kommentarer

  1. nitro2k01 said

    Tjosan! Jag sitter själv nu och försöker lära mig Oracle och jag tycker syntaxen för MS-SQL ser lite baklänges jämfört med andra typer av SQL. Dels brädgård innan tabellnamn (Vilket förvisso inte är en dum idé, men ändå känns ovant för mig) och dels avsknaden av semikolon. (Som iofs kanske inte är nödvändigt för entydighet men som jag tycker kan göra kod läsbarare ibland och kanske indirekt motverka feltänk)
    Nåväl, hur är det med prestandan för den här metoden? Sker det klassiska ”felet” att man hämtar hela tabellen för att få ut ett par rader?

    • patrik said

      Brädgården är där bara för att jag skapar en temptabell. Det används inte när man jobbar mot vanliga tabeller. Att använda semikolon är valfritt.
      Ja den kommer att hämta hela tabellen med nuvarande fråga. Om du däremot lägger till en top/limit i selectsatsen samt ett index på exempelvis team och points så kommer det gå väldigt mycket snabbare. Eftersom rank är en kolumn som behöver beräknas ‘on-the-fly’ så måste den scanna igenom alla poster och ge dessa ett värde. Det man däremot kan göra är att skapa en indexerad vy som fungerar i princip som en tabell där du då kan skapa ett index på rank och därmed kommer att kunna jobba mot den med bra prestanda.

RSS feed for comments on this post

Kommentarer inaktiverade.

%d bloggare gillar detta: