How to find the position of a row in a mysql table?

Question

Is it possible to find the position of a row in a mysql table?

For example, if we had the mysql query

SELECT * FROM `table` WHERE `Date` > X ORDER BY `Date` DESC

This will select all the rows created after X and arrange them with respect to the date they were created in. Now If I know one of these rows ID, could this query code be modified so that I know what position it is at in this query.

I mean say that the query gives 5 rows and the row which I know its ID is one of them, Is it possible to check what position it has in the query. I mean, is it 1st, 2nd, ..., 5th.

I hope I explained my problem clearly.

Using Blizz suggestion I found that

SET @I=0; SELECT @I:=@I+1 AS `I`, `ID`, `From`, `Action`, `To`, `Value`, `Date` FROM `papers` WHERE `Action` = 'Review'  GROUP BY `ID` ORDER BY `Date` DESC

Does what I want however it only works in localhost/phpmyadmin and when I run it in localhost/mysite an error comes up saying

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @I:=@rank+1 AS rank, ID, From, Action, To, Value, Date FROM `' at line 1

Does anyone know why this happens?

Hope This soloves your problem [Get row position in MYSQL query [duplicate]][1] [MySQL get row position in ORDER BY][2] [1]: http://stackoverflow.com/questions/7057772/get-row-position-in-mysql-query [2]: http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by — Rahman Qaiser, Aug 21 '15 at 08:16
Your example query is 2 statements, which the normal mysql query statements will not execute. You can rewrite it with a cross join on the select the initialise the variable @I — Kickstart, Aug 21 '15 at 08:47

score 0 · Answer 1 · answered Aug 21 '15 at 08:51

Your example SQL statement is 2 actual statements. Most of the php MySQL functions work on a single statement.

You can use a cross join to force the initialisation of the variable:-

SELECT @I:=@I+1 AS `I`, `ID`, `From`, `Action`, `To`, `Value`, `Date` 
FROM `papers` 
CROSS JOIN 
(
    SELECT @I := 0
) sub0
WHERE `Action` = 'Review'  
GROUP BY `ID` 
ORDER BY `Date` DESC

Note that there is also a possible issue with the ordering of the fields compared to where the variable is processed, and you possibly should order the data in a sub query before processing the variable to add the counter.

SELECT @I:=@I+1 AS `I`, `ID`, `From`, `Action`, `To`, `Value`, `Date` 
FROM `papers` 
(
    SELECT `ID`, `From`, `Action`, `To`, `Value`, `Date` 
    FROM `papers` 
    WHERE `Action` = 'Review'  
    GROUP BY `ID` 
    ORDER BY `Date` DESC
)
CROSS JOIN 
(
    SELECT @I := 0
) sub0
ORDER BY `Date` DESC

How to find the position of a row in a mysql table?

1 Answers1