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
