MySQL - Rank Order by Votes

October 15, 2015 - I was bored so I went to play around with MySQL and solve some simple problem or exercise below to energize my playful mind.

Write a query to rank order the following table in MySQL by votes, display the rank as one of the columns.

CREATE TABLE votes ( name CHAR(10), votes INT );

INSERT INTO votes VALUES
  ('Smith',10),
  ('Jones',15),
  ('White',20),
  ('Black',40),
  ('Green',50),
  ('Brown',20);


Solution:

SELECT
CASE
  WHEN @prevRanking = votes THEN @curRanking
  WHEN @prevRanking := votes THEN @curRanking := @curRanking+1
END AS rank,name,votes
FROM votes, (SELECT @curRanking:=0,@prevRanking:=NULL) AS t
ORDER BY votes desc;

I may be wrong but hopefully I got this right. Feel free to comment or let me know your solutions.