0

How do I find the individual rank from the below table, for same points the rank should also be the same , just like the Olympic medal table.

Table as below:

          Gold Slvr B   Total
India      44   0   0   0
Australia   7   0   0   0
Pakistan    5   0   1   6
Zimbabwe    2   2   2   5
Afghanistan 2   0   0   0
NewZealand  1   1   0   2
England     1   1   0   2
Sean3z
  • 3,745
  • 6
  • 26
  • 33
user2641435
  • 19
  • 3
  • Could you be more specific, what result do you want to achieve? – piotrgajow Mar 20 '16 at 22:21
  • I need a query to find the Rank of single country, for eg. A query to find the rank of Zimbabwe which is 3rd rank, and the the rank of newZealand and England would result a 6th rank, because they have equal number of gold , silver and bronze. So the query should sort the rank based on maximum number of gold, then max silver then max bronze and max total. – user2641435 Mar 21 '16 at 04:30

2 Answers2

1

Chances are you want something like this:

ORDER BY MAX(Gold) DESC, MAX(Slvr) DESC, MAX(B) DESC

For including a rank with each row, see Rank function in MySQL.

Community
  • 1
  • 1
ceejayoz
  • 176,543
  • 40
  • 303
  • 368
0

Got the answer as below :-

select rank,email,tgolds,tsilver,tbronze from (SELECT email, tgolds, tsilver, tbronze , @curRank := @curRank + 1 AS rank FROM results p, (SELECT @curRank := 0) r ORDER BY tgolds desc ,tsilver desc,tbronze desc,tmedal desc, date asc ) as k where k.email = '$_SESSION[email]'";

user2641435
  • 19
  • 3