On Jun 27, 7:59 am, "Roy Hann" <specia...@[EMAIL PROTECTED]
>
wrote:
> "Ed Prochak" <edproc...@[EMAIL PROTECTED]
> wrote in message
>
> news:4b1507c9-207e-4555-bd27-42dd0e84de9d@[EMAIL PROTECTED]
>
> > On Jun 26, 12:31 pm, "David Cressey" <cresse...@[EMAIL PROTECTED]
> wrote:
> > When I see
> > SELECT DISTINCT
> > in anything but an ad hoc query, I know something is wrong with the
> > query.
>
> Eh?! How can you possibly "know"any such thing? Projecting away
> distingui****ng columns is perfectly respectable, desirable and
necessary.
> And since SQL won't discrard the resulting duplicates automatically
(unless,
> bizarrely, it is in a UNION) we need to tell it to do so explicitly.
>
> > Possibly it is joining to a wrong table (to a detail table
> > instead of a header/parent table for example) or some filtering
> > condition is missed (a column not in the select list needs to be
> > used), or some join condition was missed (there is a compound key and
> > one component was left out). Even in an ad hoc query I tend to use
> > SELECT COUNT(*)
> > since that gives me a little bit more information for the same cost as
> > DISTINCT (i.e. it reads the same amount of data).
>
> > Seeing this post, I'm actually a little disappointed in you, David.
> > Your last sentence does show you mainly follow a good approach. It's
> > just that DISTINCT should be left to ad hoc queries IMO.
>
> This is not a matter to be decided on the basis of mere opinion.
>
> Roy
Keep the context in mind. David was noting cases where he was asked to
<quote> help with a query
that produces wrong results, and when I try to change their "select"
to
"select distinct" to see if maybe that's the problem </quote>
I am saying DISTINCT should be one of the last things tried in this
situation. I usually begin by asking the programmer: so exactly what
is wrong with the results? It is easier to debug code when you know
what the result should be.
But yes I stick to my opinion that DISTINCT is seldom needed in
production queries. Right now I cannot think of any case where I
needed it in a production application.
Ed
|
