List:General Discussion« Previous MessageNext Message »
From:Sergey Petrunia Date:September 15 2008 6:41pm
Subject:Re: Re[2]: SELECT DISTINCT with ORDER BY implementation
View as plain text  
Hi,
> Monday, September 15, 2008, 9:46:40 PM, you wrote:
> RJ> Maybe something like this (a huge kludge):
> RJ> SELECT id
> RJ>   FROM (SELECT ... ORDER BY ...)
> RJ>   GROUP BY ...
>
> RJ> Which will get the first id from each 'group', as ordered by the
> RJ> inner sort order.
>
> Is that guaranteed to work? I don't know how GROUP BY is implemented
> in MySQL but in general case it can't assume any order and will have
> to re-sort the sub-select result by outer GROUP BY instead of inner
> ORDER BY. If that sorting is stable, this should work, but can we rely
> on that?

Yes. This is documented behavior:
http://dev.mysql.com/doc/refman/5.0/en/select.html :
"If you use GROUP BY, output rows are sorted according to the GROUP BY
columns as if you had an ORDER BY for the same columns. To avoid the
overhead of sorting that GROUP BY produces, add ORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
"

BR
 Sergey
--
Sergey Petrunia, Lead Software Engineer
MySQL AB, www.mysql.com
Office: N/A
Blog: http://s.petrunia.net/blog
Thread
SELECT DISTINCT with ORDER BY implementationMariella Petrini10 Sep
  • RE: SELECT DISTINCT with ORDER BY implementationRick James10 Sep
    • RE: SELECT DISTINCT with ORDER BY implementationMariella Petrini10 Sep
RE: SELECT DISTINCT with ORDER BY implementationMariella Petrini11 Sep
  • Re: SELECT DISTINCT with ORDER BY implementationSergey Petrunia12 Sep
RE: SELECT DISTINCT with ORDER BY implementationMariella Petrini11 Sep