List:MySQL on Win32« Previous MessageNext Message »
From:Michael Widenius Date:September 22 1999 1:27pm
Subject:GROUP BY and ORDER BY priority
View as plain text  
>>>>> "targeted" == targeted  <targeted@stripped> writes:

targeted> Hello !
targeted> assume that we have the following table (TBL):

targeted> I(Autoincrement)    T(TimeStamp)      F(Integer)
targeted> 1                   1999-09-01        1
targeted> 2                   1999-09-02        2
targeted> 3                   1999-09-03        1

targeted> ------------------------------------------
targeted> Now issue
targeted> SELECT F FROM TBL GROUP BY F;

targeted> We get
targeted> 1
targeted> 2
targeted> of course (what else ? :)

targeted> ------------------------------------------
targeted> Now issue
targeted> SELECT F FROM TBL GROUP BY F ORDER BY T;

targeted> And get
targeted> 1
targeted> 2
targeted> this is still comprehensive :)

targeted> ------------------------------------------
targeted> Now issue
targeted> SELECT F FROM TBL GROUP BY F ORDER BY T DESC;

targeted> And get
targeted> 2
targeted> 1
targeted> This is very strange, taking into account the
targeted> previous query (without DESC).
targeted> Why isn't it 1,2 as well ?

targeted> This means that the result of the query is not
targeted> identical if you reverse the data in the table
targeted> and use DESC everywhere :)

Hi!

Note that your last query is illegal in ANSI SQL (you are not allowed
to refer to an field in ORDER by that you haven't selected).

In MySQL if you refer to an field that doesn't exists MySQL will add
this as a hidden column in the query.  The above means that you are
actually doing:

SELECT F,T FROM TBL GROUP BY F ORDER BY T DESC;

As T isn't in the GROUP BY clause it may hold any T value from the
GROUP.  This is all explained in the MySQL manual section
'Functions for use with GROUP BY clauses'

Regards,
Monty
Thread
GROUP BY and ORDER BY prioritytargeted22 Sep
  • Re: GROUP BY and ORDER BY priorityBob Kline22 Sep
  • GROUP BY and ORDER BY priorityMichael Widenius22 Sep