List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 13 2003 7:21pm
Subject:RE: Row numbers
View as plain text  
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...  |
+------------------------------------------------------------------------+
Thread
Row numbersLuc Foisy13 Feb
  • RE: Row numbersSherzod Ruzmetov13 Feb
RE: Row numbersLuc Foisy13 Feb
  • AW: Row numbersmysql13 Feb
  • Re: Row numbersJerry13 Feb
  • Re: Row numbersJerry13 Feb
RE: Row numbersJohn Griffin13 Feb
RE: Row numbersSteve Edberg13 Feb
RE: Row numbersLuc Foisy13 Feb
  • RE: Row numbersKeith C. Ivey13 Feb
    • RE: Row numbers (I thought of a new feature)Sherzod Ruzmetov13 Feb
      • Re: Row numbers (I thought of a new feature)Harald Fuchs14 Feb
RE: Row numbersSteve Edberg14 Feb
  • Re: Row numbersMichael T. Babcock14 Feb
RE: Row numbersLuc Foisy14 Feb
  • Re: Row numbersPete Harlan15 Feb
  • Re: Row numbersMichael T. Babcock16 Feb
    • Re: Row numbersCurtis Maurand16 Feb