Re: SELECT Row Numbers?

[mfatene]

Selon Chris <listschris@stripped>:

> 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/mysql?unsub=1
>
>

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:=@row+1 as rk,iTempID, sTemp
    -> FROM dist) as A
    -> WHERE iTempID=4;
+------+
| rk   |
+------+
|    4 |
+------+
1 row in set (0.00 sec)



Mathias