List:General Discussion« Previous MessageNext Message »
From:Nishikant Kapoor Date:June 28 2005 2:28pm
Subject:RE: Ordinal number within a table
View as plain text  
> -----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

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