[wp-hackers] SELECT DISTINCT .... ORDER BY not working....

[Stephane Daury]

On Nov 19, 2007, at 16:45, Computer Guru wrote:

> I'm using the latest MySQL 5.1 build, and it's not there... :-(
> There's a reason they say PostgreSQL is a better MySQL than MySQL :P

I'm not biting that bait! The flame kind. Made that mistake before. :p


> I think the problem is that stupid MySQL is insisting on doing the  
> GROUP BY
> or DISTINCT first, *then* doing the sort... (and I can't stick the  
> GROUP BY
> after the ORDER BY)

Yeah, I looked a bit more, through the suggestions listed in the doc  
page I pointed you to, but there's not to be done that doesn;t involve  
jumping through a whole bunch of hoops...

S.



> On 11/19/07, Stephane Daury <wordpress at tekartist.org> wrote:
>>
>>
>> http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html
>>
>> I'm not sure if it's available in (your version of) MySQL, but in
>> other RDBMS such as Postgres, you use "DISTINCT ON" instead of just
>> "DISTINCT".
>>
>> Stephane
>>
>>
>>
>> On Nov 19, 2007, at 16:18, Computer Guru wrote:
>>
>>> Can someone *please* tell me what is wrong with this query!? I've  
>>> been
>>> bouncing my head off the walls trying to get it to work right, but
>>> to no
>>> avail.
>>>
>>> Original query:
>>> SELECT comment_post_id FROM wp_comments
>>>  WHERE comment_approved = '1'
>>> ORDER BY comment_date DESC
>>>
>>> That works just fine. But the minute I try to get distinct
>>> comment_post_id,
>>> the sorting stops working:
>>>
>>> SELECT DISTINCT comment_post_id FROM wp_comments
>>>  WHERE comment_approved = '1'
>>> ORDER BY comment_date DESC
>>>
>>> It filters comment_post_id just fine (only one comment per post) but
>>> my
>>> results are no longer ordered correctly!
>>>
>>> Using GROUP BY instead of distinct gives me the same un-ordered
>>> output.
>>>
>>> Any advice would be mighty appreciated.
>>>
>>> --
>>> Computer Guru
>>> Director,
>>> NeoSmart Technologies
>>> http://neosmart.net/blog/
>>> _______________________________________________
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>
>
>
> -- 
> Computer Guru
> Director,
> NeoSmart Technologies
> http://neosmart.net/blog/
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers