The problem is that the value returned for a column requested
in a group query without an agregate function is undefined,
There is NO associated link between some column that you
didn't put in the function and one that does not appear
in the group by statement.. In fact usually (in my databases
including mysql this will result in an error).
Look at it this way.. What if you had two rows that corresponded
to the max, how would it know which one to pick.. What if you
were taking the Average instead of the max, and it returned 1.5
which row is it supposed to give you? As you can see there are
many examples that illustrate why you can't do that query.
Mr. Van Engen did have solutions for you posted to the list, I
will forward you his response.
It isn't that mysql can't perform the query you want, it is
that the query you have shown is not compatible with SQL
itself.
If it were me I would do it in two queries... Get the max
and then get the City corresponding to the flight and the
max, in your case you should never have two cities that corespond
to the max, but sql is a general language and cannot make such
an assumption in all cases.
Gerald Clark also posted another suggestion, another alternative,
but you're not going to be able to do this with the query you
state and it isn't mysql to blame... You can blame mysql
for not haveing subqueries though :)
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
