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:
Please note that variable assignment is within a
CASE
expression. This (theoretically) solves the evaluation order problem.IS NOT NULL
was 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 @curRank := 0)
section allows variable initialization without a separateSET
command.Test case:
result: