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

[Stephane Daury]

Yeah, that's what I didn't dare suggest given how sub-selects aren't  
always available in MySQL, and wasn't sure if you wanted to distribute  
your code (ie: a plugin or so)

S.



On Nov 19, 2007, at 17:18, Computer Guru wrote:

> I got this to work as well (subqueries):
>
> SELECT distinct comment_post_id
> FROM
> (SELECT comment_post_id FROM wp_comments
> WHERE comment_approved = '1'
> ORDER BY comment_date DESC) AS TBL
>
> About the same performance as Kimmo's query, give or take 0.001  
> seconds in
> my quick testing (0.047 vs 0.046)
>
> On 11/20/07, Stephane Daury <wordpress at tekartist.org> wrote:
>>
>>
>> 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
>>
>> _______________________________________________
>> 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