X-Received: by 10.66.66.196 with SMTP id h4mr12138083pat.5.1423416478061; Sun, 08 Feb 2015 09:27:58 -0800 (PST) X-Received: by 10.140.38.116 with SMTP id s107mr141036qgs.19.1423416478011; Sun, 08 Feb 2015 09:27:58 -0800 (PST) Path: csiph.com!usenet.pasdenom.info!news.franciliens.net!news.muarf.org!nntpfeed.proxad.net!proxad.net!feeder2-2.proxad.net!nx02.iad01.newshosting.com!newshosting.com!69.16.185.111.MISMATCH!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!hl2no12046656igb.0!news-out.google.com!q4ni11076qan.0!nntp.google.com!v8no7459999qal.1!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Sun, 8 Feb 2015 09:27:57 -0800 (PST) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=188.29.22.46; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP NNTP-Posting-Host: 188.29.22.46 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: How to I select to show only those that have more than one Horse From: rja.carnegie@gmail.com Injection-Date: Sun, 08 Feb 2015 17:27:58 +0000 Content-Type: text/plain; charset=ISO-8859-1 X-Received-Bytes: 3669 X-Received-Body-CRC: 3097639141 Xref: csiph.com comp.databases.ms-sqlserver:1884 On Sunday, 8 February 2015 15:22:23 UTC, Erland Sommarskog wrote: > Tony Johansson (johansson.andersson@telia.com) writes: > > How can I do this in MySQL or using a view. I want a more standard > > solution that works in more db then just SQL Server. > > Well, this is a forum for SQL Server, so here you will get answers for > SQL Server. If you want answers for MySQL, you need to try a MySQL forum. > > However, I believe that the query I posted is compliant with the SQL > standard, and does not include any proprietary constructs. On the other > hand, just because a query is standards-compliant is no guarantee that > it will run on all engines. I think that "WITH blah AS (yadda yadda yadda) SELECT FROM blah" is the same as "SELECT FROM (yadda yadda yadda) blah", the point being... well, various; having defined "blah" separaely, you can use it more than once in the statement, you can vary its definition while keeping the later term unchanged, or you can just regard it as "simplifying" the statement as a whole, partitioning the logic, and making it more comprehensible overall, if perhaps not any more efficient in execution. Having said that - and with continuing curiosity about whether we're doing Tony's college homework for him, or alternatively when the sporting season starts and he needs this stuff working - I think my effort would include in the first draft, WHERE ( RiderID IN ( SELECT RiderID FROM Horse GROUP BY RiderID HAVING ( COUNT(*) > 1 ) ) ) This is another way of separating the problem of "rider that is the rider of more than one horse" from the reporting. But, then again, you can "GROUP BY" lots of columns in a statement. I worry that that's inefficient - but, on the other other hand, my "RiderID IN (...)" clause is something that a server might not handle well. On the other other other hand, it may or may not avoid behaviour that I think I've noticed, at least up to SQL Server 2005, that writing "SELECT sq.* FROM ( SELECT ) sq" tends to be equivalent to "SET QUERY OPTIMIZER OFF" - I mean that the server is likely to not do well at finding an efficient execution plan for the query as a whole, and, in particular, to make proper use of indexes etc. on the inner statement's objects in the outer statement. So, asking the inner statement only to provide a heap of RiderID may avoid that failure.