I posted this question to this list on Saturday. I realize that not
everyone on the list knows whether this query is possible using MySQL.
However, if I could please here back from someone who does know, I would
appreciate it. I'm getting flack from informix ( and other DBMS ) users
on my perl user list who are now doubting whether I can get it to work
in MySQL. I have great confidence in MySQL and believe there must be a
way to get the query to work. I have searched through my MySQL/mSQL
O'Reilly book and in the user manual (specifically GROUP BY functions
(section 7.3.12)). Can someone please help me out?
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.
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
