Interesting that two people said the exact opposite with respect to performance. As we all know, there are many factors that can influence the performance of a query. If you (Carlos) have to do more multiple full table scans for a distinct, it may be time to tune your db and/or create some indices. It might be more efficient to simply do a SELECT on the fieldname you want the distinct values from and pour it through a small Perl program rather than rely on the DISTINCT clause requiring more than one FTS.
Cheers! -----Original Message----- From: Ronald J Kimball [mailto:[EMAIL PROTECTED]] Sent: January 10, 2003 10:54 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Why can't use SQL "GROUP BY..."? On Fri, Jan 10, 2003 at 10:25:43AM -0500, [EMAIL PROTECTED] wrote: > I just tried this command in Oracle: > select status_flag from status_table group by status_flag > > and it produced the same output as > select distinct status_flag from status_table > > I suspect, though I haven't confirmed this, that the two SQL commands are > optimized to the same strategy by Oracle. I'm not really sure why you would > want to use GROUP BY rather than a DISTINCT clause here. (This is under > Oracle 8.1.7, so YMMV with other databases.) With Oracle 8.1.7, using EXPLAIN PLAN for two queries like the above shows that they are not actually optimized to the same strategy; the former still does a GROUP BY. I agree, it does not make sense to use GROUP BY here instead of DISTINCT/UNIQUE. The two queries will return the same results, but using DISTINCT/UNIQUE will be faster. A similar mistake which I have seen made is to put conditionals in the HAVING clause that should be in the WHERE clause instead. For example: SELECT status, MAX(id) FROM my_table GROUP BY status HAVING status IN (1, 2) should be SELECT status, MAX(id) FROM my_table WHERE status IN (1, 2) GROUP BY status Ronald
