How about :
select a.flight_no, a.sequence, a.city
from stop_offs a left join stop_offs b
on b.sequence = a.sequence + 1 and a.flight_no = b.flight_no
where b.sequence is NULL;
Richard Reina wrote:
>
> Steve,
>
> I never received Mr. Van Engen's response. I appreciate your response.
> However, my question remains unanswered. If you put "sequence" in the
> GROUP BY it does not give you the value that correspond to the MAXIMUM
> sequence. As a matter of fact I can't find any combination of values
> that you can put in the GROUP BY clause that will give you the values
> that correspond with MAX. What good is an aggregate function like MAX
> if it does not give you the value ( and only that ) that corresponds to
> that aggregate function. In this case shouldn't (MAX(sequence)) give
> you only those values that correspond to the maximum sequence for each
> flight which would be:
>
> +-----------------------------------+
> |flight_no |sequence |City |
> | 127 | 2 |Boston |
> | 391 | 1 |Miami |
> +-----------------------------------+
>
> The documentation in the documentation under GROUP BY functions (section
> 7.3.12) seems to advocate this syntax -- specifically with their example
> of querying orders by MAX(payments) -- although they don't show the
> results of their examples.
> If this cannot be done in MySQL will someone with authority on the
> subject -- perhaps Sasha or Monty -- simply say that MySQL cannot
> perform this type of query. If it can be done will someone demonstrate
> how it can be done in MySQL. I believe I can do it in other DBMSs with
> the following subselect:
>
> SELECT soo.flight_no, soo.sequence, soo.city
> FROM stop_offs soo /* That's "stop_offs outer" */
> WHERE soo.sequence =
> (select max(soi.sequence)
> FROM stop_offs soi /* "stop_offs inner" */
> where soi.flight_no = soo.flight_no )
>
> however, it is my understanding that MySQL does not support subselects.
> I have once again included a copy of the table below.
>
> Once again, thank you for your attention in this matter.
>
> 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 |
> +-----------------------------------+
>
> Steve Ruby wrote:
> >
> > Your question was answered already after you posted it on Saturday
> > by Fred van Engen...
> >
> > Basicaly you should not be able to do a group by without perfoming an
> > aggregate function (max, avg, sum, count) on columsn that do not
> > appear on the "GROUP BY" clause.
> >
> > Richard Reina wrote:
> > >
> > > 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?
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
