Our football team has each game 'Man of the match' voting results. Now I want the TOP 3 votings (or TOP 4..5 when equal votes) backgrounded in Gold, Silver, Bronze. I prefer using SQL query instead of php-code to define the medals.
My website query returns this now:
Game - Player - Points
Arsenal - Bart - 25
Arsenal - Joris - 17
Arsenal - Tim - 16
Arsenal - Tom - 16
Arsenal - Victor - 7
Arsenal - Jo - 1
Chelsea - Tim - 15
Chelsea - Bart - 15
Chelsea - Tom - 13
Chelsea - Ken - 10
Chelsea - Victor - 7
Chelsea - Edris - 1
I am having trouble getting this result:
Game - Player - Points - Rank
Arsenal - Bart - 25 - 1
Arsenal - Joris - 17 - 2
Arsenal - Tim - 16 - 3
Arsenal - Tom - 16 - 3
Arsenal - Victor - 7 - 4
Arsenal - Jo - 1 - 5
Chelsea - Tim - 15 - 1
Chelsea - Bart - 15 - 1
Chelsea - Tom - 13 - 2
Chelsea - Ken - 10 - 3
Chelsea - Victor - 7 - 4
Chelsea - Edris - 1 - 5
Even better would be this (would be perfect, but I guess way harder)
Game - Player - Points - Rank
Arsenal - Bart - 25 - 1
Arsenal - Joris - 17 - 2
Arsenal - Tim - 16 - 3
Arsenal - Tom - 16 - 3
Arsenal - Victor - 7 - 5
Arsenal - Jo - 1 - 6
Chelsea - Tim - 15 - 1
Chelsea - Bart - 15 - 1
Chelsea - Tom - 13 - 3
Chelsea - Ken - 10 - 4
Chelsea - Victor - 7 - 5
Chelsea - Edris - 1 - 6
Any suggestions please? It is just tbl_MOTM (Id, Game, Player, Points)
Maybe a little trick with user variables can be helpful.
First, you need to define the dataset you'll be sorting. Let's assume you already have a table like this:
So your query should be something like:
So, now, let's use a variable to do what you need:
If you want to filter this to get all the players with rank 5 or above (and only five or less rows):
Of course, with a clever ussage of
case...end
you can define any conditions to reset the ranking. If you want to reset the@rank
for each game (attention: you must put the columns in a specifi order for this to work; remember that sql statements using user-variables are evaluated left-to-right and top-to-bottom):If you need the columns in a specific order, enclose the above query in a subquery, or create a temporary table (I prefer this):
Remember: temporary tables are visible only to the connection that has created it, and are deleted once the conection that created them is closed or killed.