Row position in MySQL

Question

I was looking here, but can't find the answer to my question.

Need to get a list of objects from MySQL, for e.g.:

+-----------------------------+
+ id | username | password    +
+-----------------------------+
+  1 | User1                  +
+  2 | User2                  +
+  5 | User3                  +
+  9 | User4                  +
+ 11 | User5                  +
+ 18 | User6                  +
+ .....                       +
+ 25 | User10                 +
+-----------------------------+

and count the position oh each item, not the id, but the position where item is at this moment, all this with pagination, for e.g.:

Page 1
1, User1, id1
2, User2, id2
3, User3, id5

Page 2
4, User4, id9
5, User5, id11
6, User6, id18

Page 3
...

I understand that he must calculate the position of each item in the page list on every change of the page but don't know how to do this in MySQL.

I will highly appreciate if someone will help me with an advice. Thanks

Jonathan Muller · Answer 1 · 2012-10-30T09:34:17.640

2

You can achieve this by defining a variable in your SELECT request

SET @i=0;
SELECT id, username, password, @i:=@i+1 AS i FROM my_table ORDER BY id;

edited Oct 30 '12 at 09:34

answered Oct 30 '12 at 09:28

Jonathan Muller

7,348
2
23
31

Can't get any result, the output is: Error executing query. – Denees Oct 30 '12 at 09:32
I edited my code, try the edited version with SET @i=0; instead of SELECT @i:=0; (But both should work i just tried it live on my server. can you paste your query ?) – Jonathan Muller Oct 30 '12 at 09:35
The same output :) SET @i=0; SELECT id, member_name, @i:=@i+1 AS i FROM members ORDER BY id; – Denees Oct 30 '12 at 09:36
Where do you execute your query ? In the mysql console or in your java code ? If in java this may be the issue (i dont know how hibernate works but it may parse your query before sending it to mysql and remove the var), you may have to do a stored procedure to achieve this, if you want to compute the pagination in mysql – Jonathan Muller Oct 30 '12 at 09:46
I do execute it in the MySQL Query Browser for now – Denees Oct 30 '12 at 09:53

score -1 · Answer 2 · answered Oct 30 '12 at 10:17

-1

by using a variable during select query you can get the row position. this is working on my DB. you can use this.

set @i = 0;
SELECT id, firstname, lastname, title, @i := @i +1 as i FROM employee ORDER BY id

answered Oct 30 '12 at 10:17

SAURABH_12

2,262
1
19
19

Row position in MySQL

2 Answers2