I'm looking for a row numbering in a select statement. Something I can
use to determine in whivh row values are returned in a query.
I found this insanely old list post:
http://lists.mysql.com/mysql/337
That appears to be what I want, but an examination of the changelogs for
MySQL 3.23 didn't give me any ideas.
Really what I want to do (with PHP / MySQL 4.1.?) is explained below,
any advice on that would be welcomed as well.
In PHP I have an an ID and a number, which represents number of rows.
Taking the base query and table of:
mysql> SELECT
-> iTempID,
-> sTemp
-> FROM temp;
+---------+---------+
| iTempID | sTemp |
+---------+---------+
| 1 | fred |
| 19 | barney |
| 3 | wilma |
| 4 | betty |
| 23 | bam-bam |
| 32 | pebbles |
| 7 | bart |
| 8 | lisa |
| 6 | maggie |
| 10 | homer |
| 12 | marge |
+---------+---------+
11 rows in set (0.00 sec)
I would like to specfify the id of one of the rows and a distance away
from it, and return those rows, this is how I'm trying to do it with a
row number, I'll use the fake function ROW_NUMBER() to represent the row
number.
ID: 4
Distance: 3
SELECT
iTempID,
sTemp
FROM temp
WHERE
ROW_NUMBER() BETWEEN
(SELECT ROW_NUMBER() FROM temp HAVING 4=iTempID)
AND
(SELECT ROW_NUMBER() FROM temp HAVING 4=iTempID) + 3
;
+---------+---------+
| iTempID | sTemp |
+---------+---------+
| 4 | betty |
| 23 | bam-bam |
| 32 | pebbles |
| 7 | bart |
+---------+---------+
Of course, even if there is a ROW_NUMBER() function, It may not act as I
hope in the preceding query due to the sub-queries and/or HAVING clause.
Any help would be appreciated,
Thanks,
Chris
