I've been frying my brain over this problem for the past 24 hours and I'll
be very grateful for any hints.
I'm trying to recognize when the value for a field is changing value from
one record to the next. For an example, look at this:
mysql> select sezione from faq limit 20;
+-----------+
| sezione |
+-----------+
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Macintosh |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
| Internet |
+-----------+
20 rows in set (0.01 sec)
As you see, the field's value is almost always "Internet", but it becomes
"Macintosh" in the middle. I'd like to recognize this change (and, later,
act on it).
I thought I had found the solution by employing a bit of variable magic. And
it seemed to work just fine:
mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged,
(@a:=sezione) as newValueForMemory from faq limit 20;
+-----------+------------+-------------------+
| sezione | hasChanged | newValueForMemory |
+-----------+------------+-------------------+
| Internet | yes | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Macintosh | yes | Macintosh |
| Internet | yes | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
| Internet | no | Internet |
+-----------+------------+-------------------+
20 rows in set (0.01 sec)
I keep a copy of the previous value in a variable, then check that variable
against the current value for the field. All is well.
BUT, as soon as I tackle my real problem (on a nasty database, complex
queries, and the real problem, all hell breaks loose:
mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged,
(@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where
(GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut'
or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by
ordine, SIZEMR, LENGTHMETR;
+--------+------------+-------------------+
| ordine | hasChanged | newValueForMemory |
+--------+------------+-------------------+
| 1 | yes | 1 |
| 1 | no | 1 |
| 1 | no | 1 |
| 1 | no | 1 |
| 1 | yes | 1 |
| 1 | no | 1 |
| 1 | no | 1 |
| 1 | no | 1 |
| 3 | yes | 3 |
| 3 | no | 3 |
| 3 | yes | 3 |
| 3 | no | 3 |
| 3 | no | 3 |
+--------+------------+-------------------+
13 rows in set (0.10 sec)
WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using
two variables, applying a function on the field and ever weirder ones), to
no avail. It gets even more unpredictable, as if the variables refused to be
set and reset.
mysql> select @a, ordine, @b:=ordine, @b, IF (@a=@b, 'Very Thin', 'Thin') as
divider, @a:=@b from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR
GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or
Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR;
+------+--------+------------+------+-----------+--------+
| @a | ordine | @b:=ordine | @b | divider | @a:=@b |
+------+--------+------------+------+-----------+--------+
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 1 | 1 | 1 | Very Thin | 1 |
| 1 | 3 | 3 | 1 | Very Thin | 1 |
| 1 | 3 | 3 | 1 | Very Thin | 1 |
| 1 | 3 | 3 | 1 | Very Thin | 1 |
| 1 | 3 | 3 | 1 | Very Thin | 1 |
| 1 | 3 | 3 | 1 | Very Thin | 1 |
+------+--------+------------+------+-----------+--------+
13 rows in set (0.09 sec)
Looks like a nasty bug to me (by the way, I'm running v3.23.28), but maybe
I'm just dumb? And of course the deadline is looming, etc.
What can I do? Please note that I will gladly accept a workaround, but
everything must be done inside a single MySQL query. (Which will later get
processed by a big, nasty PHP program which will do unwholesome things on it
and which I can not touch).
Thanks in advance.
Luca Accomazzi
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
