MySQL - How to determine position ranking of table sorted by two columns and allowing for ties?
I have a table (tournament) that contains a list of teams and their accumulated points and goal difference (goals scored - goals conceded).
+-----------+--------+-----------+ | team | points | goal_diff | +-----------+--------+-----------+ | USA | 7 | -2 | | Brazil | 12 | +7 | | Argentina | 12 | +10 | | Germany | 7 | -2 | | Italy | 3 | 0 | +-----------+--------+-----------+
How do I rank them by points, and then by goal difference keeping ties with the same ranking and skipping the subsequent ranking position? I'm looking for the query to yield this end result:
+-----------+--------+-----------+------+ | team | points | goal_diff | rank | +-----------+--------+-----------+------+ | Argentina | 12 | +10 | 1 | | Brazil | 12 | +7 | 2 | | USA | 7 | -2 | 3 | | Germany | 7 | -2 | 3 | | Italy | 3 | 0 | 5 | +-----------+--------+-----------+------+
I have been able to rank them and keep ties the same by the points column, but have no idea what to do to include the second column condition
SELECT s.team, s.points, s.goal_diff, (( SELECT COUNT(DISTINCT points) FROM Tournament WHERE points > s.points ) + 1) AS rank FROM Tournament s ORDER BY s.points DESC
Thanks for the help!
For anyone trying to figure this out, I ended up upgrading my DB to MariaDB 10.3.14 and used the RANK() function which does exactly what I needed here.
Here's the code:
SELECT team, points, goal_diff, RANK() OVER ( ORDER BY points DESC, goal_diff DESC ) rank FROM Tournament;
I understand upgrading the DB may not be an option for everyone, but it was a very easy solution in my case. Hope it is also helpful for others.
MySQL: How to Write a Query That Returns the Top Records in a , It's a simple orders table that spans three different customers over The rank() function is pretty cool, but it's not available prior to MySQL 8.0. We're going to start by writing a query that ranks all of the records in our table in order of Note ** using the := operand allows us to create a variable on the fly� As you can see, the second and third rows have the same ties so they receive the same rank 2. The fourth row has rank 4 because the RANK() function skips the rank 3. MySQL RANK() function example. Let’s use the sales table created in the window function tutorial for the demonstration. If you have not created the sales table yet, here is the
I missed : in #1(after @rank), that is the reason why show you blob and null value
SELECT a.team, a.points, a.goal_diff, a.rank FROM ( SELECT t.team, t.points, t.goal_diff, IF ( @points = t.points, IF (@goal_diff = t.goal_diff, @rank, @rank :=@inRank), @rank := @inRank ) AS rank, # 2 @inRank := @inRank + 1, # 3 @points := t.points, # 4 @goal_diff := t.goal_diff FROM `tournament` t, ( SELECT @points := NULL, @goal_diff := NULL, @rank := 0, @inRank := 1 ) b # 1 ORDER BY t.points DESC, t.goal_diff DESC ) a
@ is used to declare a variable. := means assign value to a variable. #1 sql is actually a variable initial.
@points : a custom declared variable for storing the points value in last row @rank : a custom declared variable for storing regular condition rank number @inRank : a custom declared variable for storing a count of row
if(boolean, trueResult, falseResult) function in mysql is kind of like a ternary operation. In #2, if the initial variable @points is equals to the value selected, it would show the value of @Rank which is 0 now. This if() function is used to judge if points values are the same.
After the if() function, we assign the variables in #3 and #4.
sql #3 increases in every row, this variable would count the row number. Thus, I can get the rank when points are not same. I think renaming @inRank to @increaseRank would be better.
sql #4 assigns the points value of this row to the variable. We use it to campare with the points value of next row.
Get the rank of a user in a score table, An index on the score column would help performance on large tables. To display scores like that requires two rank variables. rank variable to display; rank variable to calculate. Here is a more stable version of ranking with ties: Query OK, 0 rows affected (0.15 sec) mysql> CREATE TABLE scores -> ( -> id int not null� The fourth row gets the rank 4 because the RANK() function skips the rank 3. Note that if you want to have consecutive ranks, you can use the DENSE_RANK() function. SQL RANK() function examples. We will use the employees and departments table from the sample database for the demonstration.
So, basically, this is a minor edit from @wl.GIG answer. The result I get when running @wl.GIG is as below;
+-------------+----------+-------------+--------+ | team | points | goal_diff | rank | +-------------+----------+-------------+--------+ | Brazil | 12 | 7 | 1 | | Argentina | 12 | 10 | 1 | | USA | 7 | -2 | 3 | | Germany | 7 | -2 | 3 | | Italy | 3 | 0 | 5 | +-------------+----------+-------------+--------+
It's not giving the correct rank as OP's required but it's a very good approach and I'm very interested in it. So, I've made tests that can return the rank as per OP's requirement and here is what I come up with:
SELECT a.team, a.points, a.goal_diff, a.rank FROM (SELECT t.team, t.points, t.points+t.goal_diff AS tp, -- I've added this. t.goal_diff, IF (@points = t.points+t.goal_diff, --- changed this part. @rank, @rank := @inRank) AS rank, @inRank := @inRank + 1, @Points := t.points+t.goal_diff --- and changed this part. FROM `tournament` t, ( SELECT @points := NULL, @rank = 0, @inRank := 1 ) b ORDER BY t.points DESC,t.goal_diff DESC -- and added another condition in the ordering ) a;
As you can see, it wasn't a big edit but I with this, I get the result like below:
+-------------+----------+-------------+--------+ | team | points | goal_diff | rank | +-------------+----------+-------------+--------+ | Argentina | 12 | 10 | 1 | | Brazil | 12 | 7 | 2 | | USA | 7 | -2 | 3 | | Germany | 7 | -2 | 3 | | Italy | 3 | 0 | 5 | +-------------+----------+-------------+--------+
MySQL 8.0 Reference Manual :: 9.3 Keywords and , KEYWORDS table lists the words considered keywords by MySQL and indicates whether they Most of the reserved words in the list are forbidden by standard SQL as column or table names (for example, GROUP ). CHECK (R) ORDER (R) RANK (R); added in 8.0.2 (reserved) TIES ; added in 8.0.2 (nonreserved). Rank in MySQL when rows ties. Now what if we wish to assign same rank to rows with ties means rows with same values for the rank comparison column (age in our case) should hold the same rank while calculating rank in MySQL. For this purpose we have used an extra variable.
Cracking DAX – the EARLIER and RANKX Functions, The article also shows how to use the RANKX function to sort data into One more thing to do – add a calculated column to the country table, The measure will open two row contexts, as the diagram below shows: It allows you to rank by one expression, substituting in the value for Dealing with Ties. For simplicity here is only 2 users. But It may contain 1 to unlimited users. So to get rank each time you’ve to check what the status of the user, how many correct answers each user solved.
Conditional Order By, This is because T-SQL does not allow variables in these locations. 1 contains a variable as part of the expression identifying a column position. quite well, sys. all_objects, I created the following table based on a cross join, and this ensures that the two columns will match for the purposes of this demo. This is because there are two rows with a ranking of 2 and both will be returned aside from the row with ranking of 1. Breaking ties on multiple columns. The next step is to alter our ranking in such a way that it will first look at GPA, and if these are the same then look at Income, and if these are also the same then look at Date of Birth
1 MySQL - How to determine position ranking of table sorted by two columns and allowing for ties? Apr 26 '19 View all questions and answers →
- Add a counter column: stackoverflow.com/questions/2527981/…
- I really like this option. I'm fairly new to MariaDB and I'm glad there's a function like this. Good job!
- FYI, the RANK() function and other window functions support starts with MariaDB 10.2. See mariadb.com/kb/en/library/window-functions for more info
- Thank you for your answer @wl.GIG I put your code to test and I'm getting [BLOB - 1 B] and NULL values on the rank column. Unfortunately I don't understand what your code does =( and I'm unable to try and get it fixed. Could you please take a second look?
- I did some modification on @wl.GIG code because on my initial testing it also shows some NULL value. After I get it working, I noticed that one of the ranking is off. Instead of showing
- @tcadidot0 Generally, edits that change the nature of a non-CW answer shouldn't be made. I really want to be able to mark the edit as "disputed", but I can't, so I'm having to reject it. Sorry. You can write your own answer if you like!
- I correct my error and update some explanation.@JuanCarlosLau, @tcadidot0
- I modified it for the goal_diff requirement. I think it is cool now @tcadidot0
- Oh, I did not see the requirement of goal_diff. But there are two problems: 1.points+goal_diff would change the ranking rule, like points=11, goal_diff=12. It should be the third one in ranked table, and it would be first one now. 2. I think goal_diff should be treated as an absolute value
- Forget the second one, lol. I was confused to goal_diff. Now I figure it out.
- Forget the second one, lol. I was confused to goal_diff. Now I figure it out.