Ranking function in MySQL
P粉310754094
2023-08-24 00:03:12
<p>I need to find out the ranking of my customers. Here I add the corresponding ANSI standard SQL query as per my requirement. Please help me convert it to MySQL. </p>
<pre class="brush:php;toolbar:false;">SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person</pre>
<p>Is there a function for querying rankings in MySQL? </p>
This is a general solution that assigns dense levels on partitions to rows. It uses user variables:
CREATE TABLE person ( id INT NOT NULL PRIMARY KEY, firstname VARCHAR(10), gender VARCHAR(1), age INT ); INSERT INTO person (id, firstname, gender, age) VALUES (1, 'Adams', 'M', 33), (2, 'Matt', 'M', 31), (3, 'Grace', 'F', 25), (4, 'Harry', 'M', 20), (5, 'Scott', 'M', 30), (6, 'Sarah', 'F', 30), (7, 'Tony', 'M', 30), (8, 'Lucy', 'F', 27), (9, 'Zoe', 'F', 30), (10, 'Megan', 'F', 26), (11, 'Emily', 'F', 20), (12, 'Peter', 'M', 20), (13, 'John', 'M', 21), (14, 'Kate', 'F', 35), (15, 'James', 'M', 32), (16, 'Cole', 'M', 25), (17, 'Dennis', 'M', 27), (18, 'Smith', 'M', 35), (19, 'Zack', 'M', 35), (20, 'Jill', 'F', 25); SELECT person.*, @rank := CASE WHEN @partval = gender AND @rankval = age THEN @rank WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1 WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1 END AS rnk FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x ORDER BY gender, age;Please note that variable assignment is within a
CASEexpression. This (theoretically) solves the evaluation order problem.IS NOT NULLwas added to handle data type conversion and short-circuiting issues.PS: This can be easily converted to row numbers on the partition by removing all conditions that check for ties.
db demofiddle
One option is to use a ranking variable like this:
SELECT first_name, age, gender, @curRank := @curRank + 1 AS rank FROM person p, (SELECT @curRank := 0) r ORDER BY age;(SELECT @curRank := 0)section allows variable initialization without a separateSETcommand.Test case:
result: