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

[Computer Guru]

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/