CODEWITHSUNDEEP
mysqlindexing
TheBounder
TheBounder

Reputation: 1053

With MySQL, how can I generate a column containing the record index in a table?

Is there any way I can get the actual row number from a query?

I want to be able to order a table called league_girl by a field called score; and return the username and the actual row position of that username.

I'm wanting to rank the users so i can tell where a particular user is, ie. Joe is position 100 out of 200, i.e.

User Score Row
Joe  100    1
Bob  50     2
Bill 10     3

I've seen a few solutions on here but I've tried most of them and none of them actually return the row number.

I have tried this:

SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score 
       FROM league_girl GROUP BY username ORDER BY score DESC)

As derived

...but it doesn't seem to return the row position.

Any ideas?

Upvotes: 102

Views: 146495

Answers (8)

azerafati
azerafati

Reputation: 18583

I know the OP is asking for a mysql answer but since I found the other answers not working for me,

  • Most of them fail with order by
  • Or they are simply very inefficient and make your query very slow for a fat table

So to save time for others like me, just index the row after retrieving them from database

example in PHP:

$users = UserRepository::loadAllUsersAndSortByScore();

foreach($users as $index=>&$user){
    $user['rank'] = $index+1;
}

example in PHP using offset and limit for paging:

$limit = 20; //page size
$offset = 3; //page number

$users = UserRepository::loadAllUsersAndSortByScore();

foreach($users as $index=>&$user){
    $user['rank'] = $index+1+($limit*($offset-1));
}

Upvotes: 0

Heryno
Heryno

Reputation: 31

If you just want to know the position of one specific user after order by field score, you can simply select all row from your table where field score is higher than the current user score. And use row number returned + 1 to know which position of this current user.

Assuming that your table is league_girl and your primary field is id, you can use this:

SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>

Upvotes: 3

BasicExp
BasicExp

Reputation: 1

I found the original answer incredibly helpful but I also wanted to grab a certain set of rows based on the row numbers I was inserting. As such, I wrapped the entire original answer in a subquery so that I could reference the row number I was inserting.

SELECT * FROM 
( 
    SELECT *, @curRow := @curRow + 1 AS "row_number"
    FROM db.tableName, (SELECT @curRow := 0) r
) as temp
WHERE temp.row_number BETWEEN 1 and 10;

Having a subquery in a subquery is not very efficient, so it would be worth testing whether you get a better result by having your SQL server handle this query, or fetching the entire table and having the application/web server manipulate the rows after the fact.

Personally my SQL server isn't overly busy, so having it handle the nested subqueries was preferable.

Upvotes: 0

goadreamer
goadreamer

Reputation: 121

Here comes the structure of template I used:

  select
          /*this is a row number counter*/
          ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
          as rownumber,
          d3.*
  from 
  ( select d1.* from table_name d1 ) d3

And here is my working code:

select     
           ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
           as rownumber,
           d3.*
from
(   select     year( d1.date ), month( d1.date ), count( d1.id )
    from       maindatabase d1
    where      ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
    group by   YEAR( d1.date ), MONTH( d1.date ) ) d3

Upvotes: 7

Hearth
Hearth

Reputation: 393

You can also use

SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...

to initialise the counter variable.

Upvotes: 4

ftzdomino
ftzdomino

Reputation: 71

Assuming MySQL supports it, you can easily do this with a standard SQL subquery:

select 
    (count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
    username,
    score
from league_girl l2
order by score;

For large amounts of displayed results, this will be a bit slow and you will want to switch to a self join instead.

Upvotes: 3

Peter Johnson
Peter Johnson

Reputation: 2703

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo

Upvotes: 39

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

You may want to try the following:

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r;

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

Test case:

CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (3, 'c', 75);
INSERT INTO league_girl VALUES (4, 'd', 25);
INSERT INTO league_girl VALUES (5, 'e', 55);
INSERT INTO league_girl VALUES (6, 'f', 80);
INSERT INTO league_girl VALUES (7, 'g', 15);

Test query:

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r
WHERE   l.score > 50;

Result:

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)

Upvotes: 175

Related Questions