How to get position with mysql

Question

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.

Why are you skipping 2nd, 6th and 7th? There is probably a way to grab relative position - but not if you're skipping rows, it might have to be done in code. — Andy Hoffner, Mar 17 '16 at 21:43
Possible duplicate of [Rank function in MySQL](http://stackoverflow.com/questions/3333665/rank-function-in-mysql) — Alberto Chiesa, Mar 17 '16 at 21:50
@A.Chiesa, this is not a duplicate of that particular problem. See here that when for instance, there is a tie in two totals, the position skips the position after the second and goes to the next — osagie, Mar 17 '16 at 22:03
Read the second answer, please... It IS a duplicate. What you serch is the RANK function, which MySQL does not support directly, but can be emulated. You're better off searching for that. — Alberto Chiesa, Mar 17 '16 at 22:05
@ahoffner I am skipping 2nd because FoBarr2 which is the 2nd item is now in 1st position, so the next will be 3rd (this was a requirement) - This also makes it possible for the last position to tally with the total number of items. Here, I have 8 items, and the last position is 8th... — osagie, Mar 17 '16 at 22:08
@A.Chiesa,It is NOT a dupliate. Yes, in the 2nd answer there is a duplicate, but the positions are in sequential order( this is where I actually have an issue). Mine, the positions do not follow this order if there are duplicates (this is a requirement). — osagie, Mar 17 '16 at 22:14
Your rank_column is the total column. What is different from the example? — Alberto Chiesa, Mar 17 '16 at 22:21

Alberto Chiesa · Accepted Answer · 2016-03-17T22:45:22.813

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.

How to get position with mysql

1 Answers1