List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 13 2003 11:52pm
Subject:RE: Row numbers
View as plain text  
At 12:31 PM 2/13/03 , Luc Foisy wrote:
> > > I still don't understand do you mean the "actual" row
> > number or just a
> > > display number.
>
> > > > There is no relevant data or use to this number.
> > > > It is the row number of the returned result set, purely
> > > > for display.
>
>That means no field exists or should exist in the database. I only want to 
>generate at query time.
>I can't use an autoincrement field since that wont work very well with 
>results that are returned out of order and maybe not with all the data.
>
>Using variables is the best response to my question. I just dislike using 
>them cause they are ugly to work with because of the session persistance 
>and because I have to issue multiple queries to do the job.
>
>I only want to issue one query.




Extending my previous email, you could use the fact that undefined 
variables are null to combine

         set @x=0;
         select (@x:=@x+1) as row_number,name,tag_length from library_master;

into one query:

         select if(isnull(@x),@x:=1,@x:=@x+1) as row_number,name,tag_length 
from library_master;

Of course, you'll need to rest @x back to null (or 0) if you want to run 
this query again in the same session...

And as far as ugly, well - eye of the beholder, and all that stuff ;)

         -steve


+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| Database/Programming/SysAdmin                            (530)754-9127 |
| University of California, Davis             http://pgfsun.ucdavis.edu/ |
+---------------------- Gort, Klaatu barada nikto! ----------------------+

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