Here is the table:
+------------------------------+
| STOP_0FFS |
+------------------------------+
|FLIGHT_NO |CITY | SEQUENCE|
|127 |Chicago | 1 |
|127 |Boston | 2 |
|391 |Miami | 1 |
+------------------------------+
SELECT flight_no, MAX(sequence), city FROM stop_offs GROUP BY flight_no;
gives the result:
+-----------------------------------+
|flight_no |sequence |City |
| 127 | 2 |Chicago |
| 391 | 1 |Miami |
+-----------------------------------+
However Chicago is the city of the first stop off (sequence 1) not the
value that corresponds with a sequence of 2 which would be 'Boston'.
The desired result is the following:
+-----------------------------------+
|flight_no |sequence |City |
| 127 | 2 |Boston |
| 391 | 1 |Miami |
+-----------------------------------+
Where have I gone wrong in my SQL? The documentation in the manual
under GROUP BY functions (section 7.3.12) seems to advocate this syntax
although they don't show the results of their examples.
Humbled and frustrated,
Richard
---------------------------------------------------------------------
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
