List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:February 19 2001 8:17pm
Subject:Can this query not be done in MySQL.
View as plain text  
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
Thread
Can this query not be done in MySQL.Richard Reina20 Feb
  • Re: Can this query not be done in MySQL.Steve Ruby20 Feb
  • Re: Can this query not be done in MySQL.Richard Reina21 Feb
    • Re: Can this query not be done in MySQL.Michael Widenius3 Mar
  • Re: Can this query not be done in MySQL.Gerald L. Clark21 Feb
  • Re: Can this query not be done in MySQL.Steve Ruby21 Feb