On Fri, Feb 16, 2001 at 06:50:53PM -0600, Richard Reina wrote:
> 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.
>
In your case, you group on flight_no. The 'flight_no 127' group is:
+------------------------------+
| STOP_0FFS |
+------------------------------+
|FLIGHT_NO |CITY | SEQUENCE|
|127 |Chicago | 1 |
|127 |Boston | 2 |
+------------------------------+
Then you select any city of this group and the maximum sequence.
You will get either Chicago,2 or Boston,2. Which one you'll get
is not defined I believe.
You might try something like this:
select prev.flight_no, prev.city, prev.sequence
from stop_offs as prev left join stop_offs as next
on prev.flight_no = next.flight_no and prev.sequence + 1 = next.sequence
where next.sequence is null
Without the where clause, you can see what the left join selects:
select prev.flight_no, prev.city, prev.sequence,
next.flight_no, next.city, next.sequence
from stop_offs as prev left join stop_offs as next
on prev.flight_no = next.flight_no and prev.sequence + 1 = next.sequence;
cities like this:
+-----------+---------+----------+-----------+--------+----------+
| flight_no | city | sequence | flight_no | city | sequence |
+-----------+---------+----------+-----------+--------+----------+
| 127 | chicago | 1 | 127 | boston | 2 |
| 127 | boston | 2 | NULL | NULL | NULL |
| 391 | miami | 1 | NULL | NULL | NULL |
+-----------+---------+----------+-----------+--------+----------+
3 rows in set (0.01 sec)
Note that a left join will give you NULL values for the right
('next') table if no match exists (i.e. the same flight_no and
the next higher sequence number).
Using the where clause you can then select rows which have NULL
values because these contain the end cities you want.
+-----------+--------+----------+
| flight_no | city | sequence |
+-----------+--------+----------+
| 127 | boston | 2 |
| 391 | miami | 1 |
+-----------+--------+----------+
2 rows in set (0.04 sec)
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
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
