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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)