On Sun, Feb 18, 2001 at 02:19:17AM +0100, Fred van Engen wrote:
> 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;
> >
>
> 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
>
> +-----------+--------+----------+
> | flight_no | city | sequence |
> +-----------+--------+----------+
> | 127 | boston | 2 |
> | 391 | miami | 1 |
> +-----------+--------+----------+
> 2 rows in set (0.04 sec)
>
>
An alternative is to modify your query using the MAX-CONCAT trick
described in the 'Examples of Common Queries' section of the MySQL
documentation. That will probably be more efficient than a left
join.
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
