Selon Chris <[EMAIL PROTECTED]>:
> 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
hi,
your need is:
select * from temp LIMIT 3,4;
-- 3 because you have to take the fourth and 4 because dist=3+1
to find the position 4, the query is :
mysql> select rk from(SELECT @row:[EMAIL PROTECTED] as rk,iTempID, sTemp
-> FROM dist) as A
-> WHERE iTempID=4;
+------+
| rk |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
