Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1884

Re: How to I select to show only those that have more than one Horse

Show key headers only | View raw

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 <nested sub-query> ) 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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 00:47 +0100
  Re: How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 09:58 +0100
    Re: How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 10:03 +0100
  Re: How to I select to show only those that have more than one Horse Erland Sommarskog <esquel@sommarskog.se> - 2015-02-08 11:45 +0100
    Re: How to I select to show only those that have more than one Horse "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-08 12:40 +0100
      Re: How to I select to show only those that have more than one Horse Erland Sommarskog <esquel@sommarskog.se> - 2015-02-08 16:22 +0100
        Re: How to I select to show only those that have more than one Horse rja.carnegie@gmail.com - 2015-02-08 09:27 -0800
  Re: How to I select to show only those that have more than one Horse --CELKO-- <jcelko212@earthlink.net> - 2015-02-08 09:35 -0800
    Re: How to I select to show only those that have more than one Horse Ross Presser <rpresser@gmail.com> - 2015-02-09 21:21 -0800

csiph-web