You can use user variables; example:
mysql> describe library_master;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| _id | int(10) unsigned | | PRI | NULL | auto_increment |
| code | varchar(20) | | UNI | | |
| name | varchar(255) | YES | | NULL | |
| create_date | datetime | YES | | NULL | |
| tag_length | int(11) | YES | | NULL | |
| notes | text | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> set @x=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select (@x:=@x+1) as row_number,name,tag_length from library_master;
+------------+--------------+------------+
| row_number | name | tag_length |
+------------+--------------+------------+
| 1 | Callus | 17 |
| 2 | Flower | 17 |
| 3 | Leaves | 17 |
| 4 | Root | 17 |
| 5 | Silique | 17 |
| 6 | Wild flowers | 17 |
| 7 | Flowers | 17 |
| 8 | Flower | 17 |
| 9 | Flower | 17 |
+------------+--------------+------------+
9 rows in set (0.00 sec)
However, since user variables are persistent withing a session, you
have to remember to reset the value of @x; otherwise, you'll get
something like this:
mysql> select (@x:=@x+1) as row_number,name,tag_length from library_master;
+------------+--------------+------------+
| row_number | name | tag_length |
+------------+--------------+------------+
| 10 | Callus | 17 |
| 11 | Flower | 17 |
| 12 | Leaves | 17 |
| 13 | Root | 17 |
| 14 | Silique | 17 |
| 15 | Wild flowers | 17 |
| 16 | Flowers | 17 |
| 17 | Flower | 17 |
| 18 | Flower | 17 |
+------------+--------------+------------+
9 rows in set (0.00 sec)
-steve
At 1:59 PM -0500 2/13/03, Luc Foisy wrote:
>There is no relevant data or use to this number.
>It is the row number of the returned result set, purely for display.
>
>I was hoping there was some kind of function just to drop a number
>in there, regarless of any data that is stored in the table or
>regardless of the order the resultset appears.
>
>> -----Original Message-----
>> From: Jerry [mailto:jerry@stripped]
>> Sent: Thursday, February 13, 2003 1:48 PM
>> To: Luc Foisy
>> Subject: Re: Row numbers
>>
>>
>> Have to have one in the row and select that along with the
>> query, if your
>> going to use it for some other sql command it probally should
>> be in the
>> table already
>>
>> ----- Original Message -----
>> From: "Luc Foisy" <Luc.Foisy@stripped>
>> To: "Jerry" <jerry@stripped>
>> Sent: Thursday, February 13, 2003 5:59 PM
>> Subject: RE: Row numbers
>>
>>
>> > No language, just straight mysql
>> >
>> > > -----Original Message-----
>> > > From: Jerry [mailto:jerry@stripped]
>> > > Sent: Thursday, February 13, 2003 12:53 PM
>> > > To: Luc Foisy
>> > > Subject: Re: Row numbers
>> > >
>> > >
>> > > using what language ? or the mysql client ?
>> > >
>> > > ----- Original Message -----
>> > > From: "Luc Foisy" <Luc.Foisy@stripped>
>> > > To: "MYSQL-List (E-mail)" <mysql@stripped>
>> > > Sent: Thursday, February 13, 2003 5:48 PM
>> > > Subject: Row numbers
>> > >
>> > >
>> > > >
>> > > > Is there a way to get a row number returned with any
>> select query?
>> > > >
>> > > >
>> > > >
> > > >
--
+------------------------------------------------------------------------+
| Steve Edberg sbedberg@stripped |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| SETI@Home: 1001 Work units on 23 oct 2002 |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... |
+------------------------------------------------------------------------+
