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

[Computer Guru]

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 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)

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/