Re: need help for a query
Hi Claire, On Mon, 23 Aug 2004 14:52:29 -0700 (PDT), Claire Lee [EMAIL PROTECTED] wrote: Hi, Here's a table of mine namedate changeDate n1d1 cd1 n2d1 cd3 n2d2 cd1 n4d1 cd2 n1d2 cd5 n5d1 cd4 n6d2 cd2 I need to select every name for which the changeDate corresponding to d1 is greater than the changeDate corresponding to d2. Any way I can use one statement to do this? Yes, of course. You seem to be suggesting that there will only be two records with the same name in the table. In that case, something like this (although this is untested): select name from mytable a where changedate (select changedate from mytable b where a.name=b.name and a.changedate != b.changedate); or: select name from mytable a where exists (select * from mytable b where a.name=b.name and a.changedate b.changedate); I personally like the latter, form-wise. I suspect it might be marginally faster, too. If my above assumption isn't the case, we need more information on what happens when there are 3 records (do you want any record which has changedate greater than another record of the same name? or only the highest? etc.). Also, mind your NULLs. You might need to edit the above query depending on how you want to handle NULLs in changedate. Good luck, -- Matt Warden Miami University Oxford, OH http://mattwarden.com This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select non-matching fields
On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; No, actually he doesn't. SELECT ticket_number FROM purchased_items WHERE ticket_number NOT IN (SELECT ticket_number FROM purchases); The above will most certainly be faster than any join, because it is only a couple projections and a simple selection over ticket_number (which is almost certainly indexed). Although, I suppose if this is only a maintenance query (I suspect it is), then it probably doesn't matter. But, the bottom line is: if you can avoid join, do it. There's only so much the query optimizer can do. -- Matt Warden Berry Neuroscience Lab Department of Psychology Miami University This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select non-matching fields
I believe that when the query engine executes your statement, for each row of purchased_items data it looks at, it will have to run the query SELECT ticket_number FROM purchases scan those results and determine if the current row matches. If it is in the list then it wil exclude that row from the final results. I would find it very silly if mysql's query optimizer decided that the optimized way to execute the query is to execute SELECT ticket_number FROM purchases N times, where N is the number of rows in purchased_items. There is no reason why that query would be executed any more than one time. FWIW, there is a correlated subquery version of this query (the example I gave is uncorrelated), and it would be the following: SELECT ticket_number FROM purchased_items a WHERE NOT EXISTS (SELECT * FROM purchases b WHERE a.ticket_number=b.ticket_number); I cannot say for certain that these two queries are not executed by mysql in the same manner, but I would be surprised if they were. I say this because you could alter my query and use an explicit set: SELECT ticket_number FROM purchased_items WHERE ticket_number NOT IN (112, 456, 942, 356, 623, 783); I would find it more likely that the above query is executed in the same way as my original solution. And, Emmet Bishop insightfully commented: You're making the assumption that he's using 4.1.x. He didn't state which version he's using so your solution may be of no use to him. Good point. I often forget about which features are/were unimplemented in mysql. My apologies. -- Matt Warden Berry Neuroscience Lab Miami University http://mattwarden.com This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
