"Ed Prochak" <edprochak@[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
|
