>>>>> "Xeno" == Xeno Campanoli <[EMAIL PROTECTED]> writes:
Xeno> I've got some perl code I wrote as follows that has two parts which are
Xeno> basically the same logic, but one looks one way in a key sequence, using min,
Xeno> the other goes the other way, using max, as follows:
Xeno> if ( $increment > 0 )
Xeno> {
Xeno> $d_query_string = "select min(acct_no), $fieldlist from customer "
Xeno> . " where branch_id=\'$branch_id\' "
Xeno> . " and acct_no>\'$acct_no\' ";
Xeno> $relword = "after";
Xeno> }
Xeno> elsif ( $increment < 0 )
Xeno> {
Xeno> $d_query_string = "select max(acct_no), $fieldlist from customer "
Xeno> . " where branch_id=\'$branch_id\' "
Xeno> . " and acct_no < \'$acct_no\' ";
Xeno> $relword = "before";
Xeno> }
Xeno> branch_id is char(3), and acct_no is char(10) and are the only two members of
Xeno> the primary key, mentioned in the order I give here. The intention here (which
Xeno> by the way may be done better with another method, and if so I'd like to know
Xeno> it, but the failure I think is still important, and I should mention, I haven't
Xeno> tried this on 3.22, only 3.21) is to pick the next (if increment is 1) or
Xeno> preceding (if $increment is -1) acct_no row in the sequence. Plainly stated,
Xeno> I can get the next one, but not the preceding, with the above code (which is
Xeno> perl, oh yes I said that). I think when I tried the code for the preceding
Xeno> I got some minimum key in the table for the given branch_id. So, for example
Xeno> with $increment 1, present acct_no at 20000, and the nearest next acct_no 20005,
Xeno> I get the row with that acct_no 20005. When the $increment is -1, and the
Xeno> nearest preceding is 19009, and the smallest key value is 10000, I get the row
Xeno> with key value 10000. Unless I'm crazy this is at least a logical inconsistency
Xeno> although I'd be reasonably credulous if someone claimed it complied with SQL
Xeno> standards somehow. If this is a bug, can it be (has it been) fixed (please?)?
Hi!
The problem is that in your query:
select max(acct_no), $fieldlist from customer where branch_id=\'$branch_id\' and
acct_no < \'$acct_no\' ";
The columns in 'fieldlist' will contain ANY value that matches the
WHERE clause.
In strict SQL the above query is meaningless (you would be required to
have a GROUP BY for every column in '$fieldlist'); MySQL supports an
extension for ANSI SQL that allowes you to use fields in the SELECT
part that you don't have in GROUP BY, but it doesn't do what you
expect it do to.
You can find this described at:
http://www.tcx.se/Manual_chapter/manual_toc.html
section: Group by functions
The fix in your case is to do the above in 2 queries; First get the
max/min and after this get the matching row.
In MySQL 3.22, the MAX/MIN query is very fast if used on keys!
Regards,
Monty
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
