List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:February 17 2001 12:50am
Subject:SQL stumper or MySQL bug?
View as plain text  
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
Thread
SQL stumper or MySQL bug?Richard Reina18 Feb
  • Re: SQL stumper or MySQL bug?Fred van Engen18 Feb
    • Re: SQL stumper or MySQL bug?Fred van Engen18 Feb