RE: Row numbers

[Steve Edberg]

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:[EMAIL PROTECTED]]
 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" <[EMAIL PROTECTED]>
 To: "Jerry" <[EMAIL PROTECTED]>
 Sent: Thursday, February 13, 2003 5:59 PM
 Subject: RE: Row numbers


 > No language, just straight mysql
 >
 > > -----Original Message-----
 > > From: Jerry [mailto:[EMAIL PROTECTED]]
 > > 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" <[EMAIL PROTECTED]>
 > > To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]>
 > > 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                                      [EMAIL PROTECTED] |
| 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...  |
+------------------------------------------------------------------------+

---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php