List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 29 2005 4:19am
Subject:Re: Ordinal number within a table
View as plain text  
Kapoor, Nishikant wrote:

 > [Sorry for cross-posting.]
 >
 > 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 ...

You cannot execute them together.  You must execute them one at a time, in the 
same connection.

 > ... or else I get
 >
 > +-----+--------+-------+
 > | row | ename  | empno |
 > +-----+--------+-------+
 > |NULL | SMITH  |  7369 |
 > |NULL | ALLEN  |  7499 |
 > |NULL | WARD   |  7521 |
 > |NULL | JONES  |  7566 |
 > |NULL | MARTIN |  7654 |
 > +-----+--------+-------+

If you are getting this, you've made a mistake in your perl code.  It's hard 
to say what, though, as you haven't shown us your code.

Hmmm.  User variables are connection specific.  Are you making the mistake of 
opening and closing a connection for each query?  That's unneccessary, and it 
adds a lot of overhead.

 > How can I execute both (a) and (b) in my perl script?

The same way you would execute any two statements, one at a time.  Something like:

   $conn->do('SET @row=0');
   my $sql = 'SELECT @row:=@row+1 as row, <fields> FROM <tables> WHERE ...';
   my $sth = $conn->prepare($sql);
   $sth->execute();
   return $sth->fetchall_arrayref( {} );

 > Thanks for any help.
 > Nishi

Mathias wrote:

 > Hi,
 > You don"t need to use @row in perl,
 > just use :
 >
 > $n=0;
 > while (fetch..) {
 > $n++;
 > print "$n"."$ename ...\n";
 >
 > }

That would work.

Kapoor, Nishikant wrote:

 > 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

You are returning an arrayref!  One row in your results equals one row in your 
array -- in the same order!  Arrays are indexed, so display data order is 
already built into your array.  Display position = array position + 1.  Why do 
you need a redundant field in each row?

Harald Fuchs wrote:

 > Just change the last line to
 >
 >   my $n = 0;
 >   return [ map { [ ++$n, @$_ ] } @{$sth->fetchall_arrayref} ];
 >
 > What's the problem?

Are you sure?  I get "Can't coerce array into hash at...".

Mathias wrote:

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

No, no, no!  This is what we call using a shotgun to kill a gnat.  It also 
doesn't yield the order of the query results.

Michael
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