List:General Discussion« Previous MessageNext Message »
From:mfatene Date:June 28 2005 2:37pm
Subject:RE: Ordinal number within a table
View as plain text  
Selon "Kapoor, Nishikant" <nishikant.x.kapoor@stripped>:

>
> > -----Original Message-----
> > > This is in continuation with the above mentioned subject -
> > I am trying to
> > > find the 'display data order' for the returned resultset.
> > The following
> > > thread very well answers my question:
> > >
> > >  http://lists.mysql.com/mysql/185626
> > >
> > > a) SET @row=0;
> > > b) SELECT (@row:=@row+1) AS row, ename, empno FROM emp
> > ORDER BY empno;
> > >
> > > +-----+--------+-------+
> > > | row | ename  | empno |
> > > +-----+--------+-------+
> > > |   1 | SMITH  |  7369 |
> > > |   2 | ALLEN  |  7499 |
> > > |   3 | WARD   |  7521 |
> > > |   4 | JONES  |  7566 |
> > > |   5 | MARTIN |  7654 |
> > > +-----+--------+-------+
> > >
> > > However, I am trying to use it in a perl script instead of
> > from command line,
> > > and I am not sure how exactly to do it. I need to execute
> > both statements a &
> > > b together or else I get
> > >
> > > +-----+--------+-------+
> > > | row | ename  | empno |
> > > +-----+--------+-------+
> > > |NULL | SMITH  |  7369 |
> > > |NULL | ALLEN  |  7499 |
> > > |NULL | WARD   |  7521 |
> > > |NULL | JONES  |  7566 |
> > > |NULL | MARTIN |  7654 |
> > > +-----+--------+-------+
> > >
> > > How can I execute both (a) and (b) in my perl script?
> > >
> > > Thanks for any help.
> > > Nishi
>
> Hi,
> You don"t need to use @row in perl,
> just use :
>
> $n=0;
> while (fetch..) {
> $n++;
> print "$n"."$ename ...\n";
>
> }
>
> Mathias
>
> I could, but I am assigning the entire resultset in one shot to another
> construct as follows:
>
> my $str  = "SELECT \@row:=\@row+1 as row, <fields> FROM <tables> WHERE
> ...";
> my $sth = $conn->prepare($st);
> $sth->execute();
> return $sth->fetchall_arrayref( {} );
>
> Thanks,
> -Nishi
>
>


Then alter your table to add an auto_increment column, update it and play your
query without @row.

Mathias
Thread
Ordinal number within a tableEd Reed18 Jun
  • Re: Ordinal number within a tableDan Nelson18 Jun
  • Re: Ordinal number within a tableJigal van Hemert18 Jun
    • Re: Ordinal number within a tablemfatene18 Jun
Re: Ordinal number within a tableNishikant Kapoor28 Jun
  • Re: Ordinal number within a tablemfatene28 Jun
RE: Ordinal number within a tableNishikant Kapoor28 Jun
  • RE: Ordinal number within a tablemfatene28 Jun
    • Re: Ordinal number within a tableMichael Stassen29 Jun
      • Re: Ordinal number within a tableMathias29 Jun