njtynhj

I am not an expert in MySQL . I need to find out rank of customers. Here I am adding the corresponding ANSI standard SQL query for my requirement. Please help me to convert it to MySQL .

Is there any function to find out rank in MySQL?

One option is to use a ranking variable, such as the following:

The (SELECT @curRank := 0) part allows the variable initialization without requiring a separate SET command.

Test case:

Result:

Here is a generic solution that sorts a table based on a column and assigns rank; rows with ties are assigned same rank (uses an extra variable for this purpose):

Note that there are two assignment statements in the second WHEN clause. Sample data:

Output:

SQL Fiddle

While the most upvoted answer ranks, it doesn't partition, You can do a self Join to get the whole thing partitioned also:

Use Case

Answer:

A tweak of Daniel's version to calculate percentile along with rank. Also two people with same marks will get the same rank.

Results of the query for a sample data -

Combination of Daniel's and Salman's answer. However the rank will not give as continues sequence with ties exists . Instead it skips the rank to next. So maximum always reach row count.

Schema and Test Case:

Output:

@Sam, your point is excellent in concept but I think you misunderstood what the MySQL docs are saying on the referenced page -- or I misunderstand :-) -- and I just wanted to add this so that if someone feels uncomfortable with the @Daniel's answer they'll be more reassured or at least dig a little deeper.

You see the "@curRank := @curRank + 1 AS rank" inside the SELECT is not "one statement", it's one "atomic" part of the statement so it should be safe.

The document you reference goes on to show examples where the same user-defined variable in 2 (atomic) parts of the statement, for example, "SELECT @curRank, @curRank := @curRank + 1 AS rank".

One might argue that @curRank is used twice in @Daniel's answer: (1) the "@curRank := @curRank + 1 AS rank" and (2) the "(SELECT @curRank := 0) r" but since the second usage is part of the FROM clause, I'm pretty sure it is guaranteed to be evaluated first; essentially making it a second, and preceding, statement.

In fact, on that same MySQL docs page you referenced, you'll see the same solution in the comments -- it could be where @Daniel got it from; yeah, I know that it's the comments but it is comments on the official docs page and that does carry some weight.

If you want to rank just one person you can do the following:

This ranking corresponds to the oracle RANK function (Where if you have people with the same age they get the same rank, and the ranking after that is non-consecutive).

It's a little bit faster than using one of the above solutions in a subquery and selecting from that to get the ranking of one person.

This can be used to rank everyone but it's slower than the above solutions.

The most straight forward solution to determine the rank of a given value is to count the number of values before it. Suppose we have the following values:

Now back to the original question. Here is some sample data which is sorted as described in OP (expected ranks are added on the right):

To calculate RANK() OVER (PARTITION BY Gender ORDER BY Age) for Sarah, you can use this query:

To calculate RANK() OVER (PARTITION BY Gender ORDER BY Age) for All rows you can use this query:

And here is the result (joined values are added on right):

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.