RE: Ordinal number within a table

2005-06-29 Thread Jamie McCarthy 
It's probably much faster to do this in perl, since the use of @row
means MySQL can't use its query cache.
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-28 Thread Kapoor, Nishikant 
[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:[EMAIL PROTECTED]) 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-28 Thread mfatene 
Selon Kapoor, Nishikant [EMAIL PROTECTED]:

 [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:[EMAIL PROTECTED]) 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


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




Hi,
You dont need to use @row in perl,
just use :

$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;

}


Mathias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Ordinal number within a table

2005-06-28 Thread Kapoor, Nishikant 

 -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:[EMAIL PROTECTED]) 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 dont 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 [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, fields FROM 
tables WHERE ...;
my $sth = $conn-prepare($st);
$sth-execute();
return $sth-fetchall_arrayref( {} );

Thanks,
-Nishi


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Ordinal number within a table

2005-06-28 Thread mfatene 
Selon Kapoor, Nishikant [EMAIL PROTECTED]:


  -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:[EMAIL PROTECTED]) 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 dont 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 [EMAIL PROTECTED]:[EMAIL PROTECTED] 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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-28 Thread Michael Stassen 

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:[EMAIL PROTECTED]) 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:[EMAIL PROTECTED] 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 dont 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 [EMAIL PROTECTED]:[EMAIL PROTECTED] 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/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-28 Thread Mathias 
Selon Michael Stassen [EMAIL PROTECTED]:

 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:[EMAIL PROTECTED]) 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:[EMAIL PROTECTED] 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 dont 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 [EMAIL PROTECTED]:[EMAIL PROTECTED] 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/[EMAIL PROTECTED]



 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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-18 Thread mfatene 
hi,
the position of a record depend on the sort order you choose in your queries
(generally order by) and the execution plan of them.

this execution plan depends on data, indexes, and the query itself. So what you
call order is candidate to changing between two selects.

the method shown with @row++ is a solution, but the given order is the display
data order for you. use an order by so make it stronger.

Mathias

Selon Jigal van Hemert [EMAIL PROTECTED]:

 From: Ed Reed

  Is there way to return the ordinal position of a value within a table?
 
  Let's say I have a table of phone numbers. Over time the table has had
 additions and deletions. The table has an autonumber ID field. If I sort by
 the ID field I'd like to know what position the number '555-1212' is in the
 table.

 I'm not sure why you'd want to know this, but it's generally a good idea to
 abandon the thought that records in a database are stored in a certain order
 with a position number attached to them.

 The internal way of storing data differs from engine to engine and you can
 never be sure that these internals will not be modified in newer releases of
 MySQL.

 It's best to think of a table as a collection of records which can be
 presented in the way you want. The way the database decides to store the
 data is in many cases not relevant at all; that's the job of the database.

 Regards, Jigal.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-17 Thread Dan Nelson 
In the last episode (Jun 17), Ed Reed said:
 Is there way to return the ordinal position of a value within a table?
  
 Let's say I have a table of phone numbers. Over time the table has
 had additions and deletions. The table has an autonumber ID field. If
 I sort by the ID field I'd like to know what position the number
 '555-1212' is in the table.

In Oracle you could simply use the internal rownum column.  In MySQL,
you can use a user variable in a subquery to keep a row count during
the select process:

SET @row=0;
SELECT (@row:[EMAIL PROTECTED]) 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 |
|   6 | BLAKE  |  7698 |
|   7 | CLARK  |  7782 |
|   8 | SCOTT  |  7788 |
|   9 | KING   |  7839 |
|  10 | TURNER |  7844 |
|  11 | ADAMS  |  7876 |
|  12 | JAMES  |  7900 |
|  13 | FORD   |  7902 |
|  14 | MILLER |  7934 |
+-++---+
SET @row=0;
SELECT * FROM (
 SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY empno
 ) t WHERE ename='scott';
+-+---+---+
| row | ename | empno |
+-+---+---+
|   8 | SCOTT |  7788 |
+-+---+---+

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ordinal number within a table

2005-06-17 Thread Jigal van Hemert 
From: Ed Reed

 Is there way to return the ordinal position of a value within a table?

 Let's say I have a table of phone numbers. Over time the table has had
additions and deletions. The table has an autonumber ID field. If I sort by
the ID field I'd like to know what position the number '555-1212' is in the
table.

I'm not sure why you'd want to know this, but it's generally a good idea to
abandon the thought that records in a database are stored in a certain order
with a position number attached to them.

The internal way of storing data differs from engine to engine and you can
never be sure that these internals will not be modified in newer releases of
MySQL.

It's best to think of a table as a collection of records which can be
presented in the way you want. The way the database decides to store the
data is in many cases not relevant at all; that's the job of the database.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]