> I find by experiment that > (select * from FOO order by a desc limit 10) order by a; > removes duplicates, but, if I drop the second order clause, > (select * from FOO order by a desc limit 10); > duplicates are retained. > > Why is the first a union, but not the second? Just curious.
On http://dev.mysql.com/doc/mysql/en/UNION.html you can see a comment by Keith Ivey about this. Apparantly it's caused by the fact that (SELECT .....) UNION (SELECT....) [ORDER BY ....] is the syntax for a UNION. If you leave the first table out, you're left with: (SELECT ....) ORDER BY... The fact that there are parentheses and an ORDER BY outside these parentheses seems to make it a UNION. If you leave out the ORDER BY..., it's just a query with parentheses around it. The manual states that if you do not use the keyword ALL with the UNION, it's considered to be DISTINCT. So, leaving out the UNION keyword entirely automatically makes it using DISTINCT. Regards, Jigal. > > From: "Keith C. Ivey" <[EMAIL PROTECTED]> > > DuBois: This one-query union syntax doesn't allow you to use the ALL > > keyword after UNION (since the UNION keyword isn't even there). That > > means it will always eliminate duplicate rows (like DISTINCT). That > > hasn't come up when I've used it, since I've never been selecting > > result sets that could contain duplicate rows, but it's something to > > keep in mind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
