Hi!
Sorry for the late reply, but I was away for a one week work/vacation
trip to Rio.
>>>>> "Richard" == Richard Reina <[EMAIL PROTECTED]> writes:
Richard> Steve,
Richard> I never received Mr. Van Engen's response. I appreciate your response.
Richard> However, my question remains unanswered. If you put "sequence" in the
Richard> GROUP BY it does not give you the value that correspond to the MAXIMUM
Richard> sequence. As a matter of fact I can't find any combination of values
Richard> that you can put in the GROUP BY clause that will give you the values
Richard> that correspond with MAX. What good is an aggregate function like MAX
Richard> if it does not give you the value ( and only that ) that corresponds to
Richard> that aggregate function. In this case shouldn't (MAX(sequence)) give
Richard> you only those values that correspond to the maximum sequence for each
Richard> flight which would be:
Richard> +-----------------------------------+
Richard> |flight_no |sequence |City |
Richard> | 127 | 2 |Boston |
Richard> | 391 | 1 |Miami |
Richard> +-----------------------------------+
Richard> The documentation in the documentation under GROUP BY functions (section
Richard> 7.3.12) seems to advocate this syntax -- specifically with their example
Richard> of querying orders by MAX(payments) -- although they don't show the
Richard> results of their examples.
Richard> If this cannot be done in MySQL will someone with authority on the
Richard> subject -- perhaps Sasha or Monty -- simply say that MySQL cannot
Richard> perform this type of query. If it can be done will someone demonstrate
Richard> how it can be done in MySQL. I believe I can do it in other DBMSs with
Richard> the following subselect:
Richard> SELECT soo.flight_no, soo.sequence, soo.city
Richard> FROM stop_offs soo /* That's "stop_offs outer" */
Richard> WHERE soo.sequence =
Richard> (select max(soi.sequence)
Richard> FROM stop_offs soi /* "stop_offs inner" */
Richard> where soi.flight_no = soo.flight_no )
Richard> however, it is my understanding that MySQL does not support subselects.
Richard> I have once again included a copy of the table below.
Richard> Once again, thank you for your attention in this matter.
Richard> Here is the table:
Richard> +------------------------------+
Richard> | STOP_0FFS |
Richard> +------------------------------+
Richard> |FLIGHT_NO |CITY | SEQUENCE|
Richard> |127 |Chicago | 1 |
Richard> |127 |Boston | 2 |
Richard> |391 |Miami | 1 |
Richard> +------------------------------+
Richard> SELECT flight_no, MAX(sequence), city FROM stop_offs GROUP BY flight_no;
Richard> gives the result:
Richard> +-----------------------------------+
Richard> |flight_no |sequence |City |
Richard> | 127 | 2 |Chicago |
Richard> | 391 | 1 |Miami |
Richard> +-----------------------------------+
Richard> However Chicago is the city of the first stop off (sequence 1) not the
Richard> value that corresponds with a sequence of 2 which would be 'Boston'.
Richard> The desired result is the following:
Richard> +-----------------------------------+
Richard> |flight_no |sequence |City |
Richard> | 127 | 2 |Boston |
Richard> | 391 | 1 |Miami |
Richard> +-----------------------------------+
Without sub selects one can't do this easily, but it's still possible
to do by using the trick that is described in the MySQL manual under
the GROUP BY section:
Until MySQL has sub selects, you can use the following query:
select flight_no, MAX(sequence), substring(MAX(concat(rpad(sequence,2,' '),city)),3)
from stops GROUP BY flight_no;
It's not that elegant, but its works (and is even in some cases faster
than sub selects)!
Regards,
Monty
---------------------------------------------------------------------
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
