List:General Discussion« Previous MessageNext Message »
From:Chris Date:May 9 2005 10:08pm
Subject:SELECT Row Numbers?
View as plain text  
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

Thread
SELECT Row Numbers?Chris10 May
  • Re: SELECT Row Numbers?mfatene10 May
    • Re: SELECT Row Numbers?Chris10 May
  • Re: SELECT Row Numbers?Harald Fuchs10 May
    • Re: SELECT Row Numbers?Marco Neves10 May
    • Re: SELECT Row Numbers?Chris10 May
      • Re: SELECT Row Numbers?SGreen10 May
        • Re: SELECT Row Numbers?Chris10 May
  • Re: SELECT Row Numbers?Rhino10 May
    • Re: SELECT Row Numbers?mfatene10 May
  • Re: SELECT Row Numbers?Harald Fuchs10 May
    • Re: SELECT Row Numbers?Alec.Cawley10 May
    • Re: SELECT Row Numbers?Harald Fuchs10 May
      • Re: SELECT Row Numbers?Eric Bergen10 May