Get Overall Rank

Question

I run the below sql server script to retrieve some data , but I need to add column to tell me in each LEAGUE which user is first, second and so on

Select 

(Select Name From League Where ID = League_Details.League_ID) As League
,Player
,(Select Total From AllUserPoints Where User_ID = Player) As Total


 From 
     League_Details

Where 
     LeagueType = 'Private'

order by League

this is the result:

like this: Select b.Name ,a.Player ,c.Total From League_Details a join League b on a.League_ID = b.ID join AllUserPoints c on a.Player = c.User_ID Where a.LeagueType = 'Private Classic League' order by a.League_ID — Ikrami Hall, Nov 24 '20 at 16:57
And good aliases. `b` for `League`? There's no "b" in "League". — Thom A, Nov 24 '20 at 16:57
join League b on a.League_ID = b.ID because i get the name of the league depend on ID — Ikrami Hall, Nov 24 '20 at 17:02

score 0 · Accepted Answer · answered Nov 24 '20 at 17:08

0

You have to use order by properly in this statement. Try this one:

Select (Select Name From League Where ID = League_Details.League_ID) As League
,Player, (Select Total From AllUserPoints Where User_ID = Player) As Total

 From 
     League_Details

Where 
     LeagueType = 'Private'

order by League, Total desc

Try this and Let me know whether it works on not.

answered Nov 24 '20 at 17:08

Rokan Chowdhury Onick

309
1
8

Working for sorting but what i need adding column with rank order: Rank 1 1 1 2 – Ikrami Hall Nov 24 '20 at 17:14
@IkramiHall Ok, Then you can check this out: https://stackoverflow.com/questions/3333665/rank-function-in-mysql – Rokan Chowdhury Onick Nov 24 '20 at 17:35
Thank you it's DONE: – Ikrami Hall Nov 24 '20 at 18:09
1

Final Script, i added offset 0 rows because i store the script into view Select b.Name as League ,a.Player ,c.Total ,RANK() OVER (PARTITION BY b.Name ORDER BY c.Total desc) AS Current_Rank From League_Details a join League b on a.League_ID = b.ID join AllUserPoints c on a.Player = c.User_ID Where a.LeagueType = 'Private' order by b.Name, c.Total desc offset 0 rows – Ikrami Hall Nov 24 '20 at 18:11

Get Overall Rank

1 Answers1