SQL ranking

[buladex]

 

i have this table called honor in my sql:

 

 

 

id  player_id  player_name win   lose  points
26	15098570	Ms.(EviL)	1	0	50
27	15097606	NADA	0	1	0
28	15098570	Ms.(EviL)	1	0	50
29	15096607	~ABO.MoTa~	1	0	50
30	15097330	!~!HaSSn!~!	0	1	0
31	15098760	*«EmiäêM»*	1	0	50
32	15097702	~SworDoFLight~	0	1	0
33	15098760	*«EmiäêM»*	1	0	50
34	15097378	fager	0	1	0
35	15098760	*«EmiäêM»*	1	0	50
36	15098712	^kill^TO^live^	0	1	0
37	15097702	~SworDoFLight~	1	0	50
38	15098760	*«EmiäêM»*	0	1	0
39	15094095	~SwordOfLiGht~	1	0	50
40	15097702	~SworDoFLight~	0	1	0
41	15094095	~SwordOfLiGht~	1	0	50
42	15094095	~SwordOfLiGht~	0	1	0
43	15098712	^kill^TO^live^	1	0	50

 

 

and i want to make a rank page for it like this:

 

 
Rank       Name      Win      Lose
  1        ABCD     100       50
  2        PROX      99       60

 

can anyone  help me make this???please

[Mchl]

Simplest case:

 

SELECT @rank := @rank+1 AS rank, player_name, win, lose 
FROM honor
CROSS JOIN (SELECT @rank := 0) AS sq
ORDER BY win - lose

 

for ex aequo rankings the code is a bit more complicated. You will find several examples when you google for 'MySQL ranking'

[buladex]

Mchl  ur example doesnt  make sum from win and lose

[Mchl]

Ah.. it wasn't obvoius that it should

 

SELECT @rank := @rank+1 AS rank, h.player_name, h.win, h.lose
FROM (
  SELECT player_id, player_name, SUM(win) AS win, SUM(lose) AS lose FROM honor GROUP BY player_id
) AS h
CROSS JOIN (SELECT @rank := 0) AS sq
ORDER BY win - lose

[buladex]

SELECT player_name, SUM(win) AS win, SUM(lose) AS lose FROM honor GROUP BY player_name

 

better? cuz i dont need player id on rank table thats for serv side only