In some cases we want to know position of specific row in result set. For example when we use pagination and want to know on which page specific element is. If we don’t use ORDER BY with non-unique field, we can use simple query:
SELECT COUNT(*) FROM TABLE WHERE id <= ELEMENT_ID
The result will be position of element with id equal to ELEMENT_ID, assuming that result is ordered by id field. If we use other field in ORDER BY, we have to change WHERE clause, for instance:
SELECT COUNT(*) FROM TABLE r WHERE creation_time >= ELEMENT_CREATION_TIME_FIELD ORDER BY creation_time DESC
Note that in WHERE clause greater-equal is used instead of less-equal, because DESC instead of ASC (default) is used.
Unfortunately this approach won’t work when we use ORDER BY with non-unique field. In that case we have to use MySQL user variable.
Example: getting row position for row identified by specified id.
SET @i = 0; SELECT POSITION FROM ( SELECT id, @i:=@i+1 AS POSITION FROM TABLE ti ) t WHERE id=ID
Caution: if you use PHP, remember that you have to execute two queries, first for SET and second for SELECT one.
If you want to use SELECT query more than once, remember to set i variable each time.
As you can see above – trick is about incrementing i variable on each row, and fetching position of interesting row.
We can also use this technique when result set is ordered.
Example: getting row position for row identified by specified id in ordered query
SELECT POSITION FROM ( SELECT id, @i:=@i+1 AS POSITION FROM TABLE ti ORDER BY creation_time DESC ) t WHERE id = ID;
Another thing we can achieve is getting row located on specified position.
Example: Getting row located on position 2
SELECT id FROM ( SELECT id, @i:=@i+1 AS POSITION FROM TABLE ti ORDER BY creation_time DESC ) t WHERE POSITION = 2;