CODEWITHSUNDEEP
phpmysql
osagie
osagie

Reputation: 269

How to get position with mysql

I have a table like this: 

Name         Total
Foo          7
FoBarr       2
Fobarr2      2
Foobar       5
FBar         8
FBar2        8
FBar3        8
FbarL        9

What I actually want is not a way to get the position like this:

Name    Total    Position
FoBarr     2        1st
FoBarr2    2        1st
Foobar     5        3rd     // notice that there is no 2nd position
Foo        7        4th
FBar       8        5th
FBar2      8        5th
FBar3      8        5th
FbarL      9        8th    // notice that there is no 6th and 7th...

I would appreciate an SQL query solution for this, though I'm working on a php application too.

I have searched, and I believe this problem does not exactly fall under the rank function as such, because the position does not just go on sequentially like that if there are ties.

Thanks.

Upvotes: 0

Views: 48

Answers (1)

Alberto Chiesa
Alberto Chiesa

Reputation: 7350

Oooook, so, if this is not an actual duplicate, let me just elaborate on the answer I linked before:

SET @prev_value = NULL;
SET @count = 0;
SET @rank_count = 0;
SELECT Name, total,
@count := @count + 1 as RowNumber,
CASE
    WHEN @prev_value = total THEN @rank_count
    WHEN @prev_value := total THEN @rank_count := @count
END AS Position
FROM rank_table
ORDER BY total

What you want to do is take 2 counters, 1 is going to be incremented by one for every row, the other is going to keep the ranking value, to be reset to the first counter when the value changes.

See here for a SQL Fiddle.

Upvotes: 1

Related Questions