List:General Discussion« Previous MessageNext Message »
From:Mathias Date:June 29 2005 5:02am
Subject:Re: Ordinal number within a table
View as plain text  
Selon Michael Stassen <Michael.Stassen@stripped>:

> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

> 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.

sure that ${$ligne[$row]}{$n}:=$n with n perl operations can be faster  !:)

Hope that helps
:o)
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