Yes, it's legal, but with that syntax the "DISTINCT" applies to the COUNT, not
to the field(s). For example, given a table named Employees with fields
{EmpID, EmpName, EmpType} and these rows:
1, Smith, 4
2, Jones, 7
3, Doe, 4
4, Johnson, 8
There are 3 distinct values of EmpType, but SELECT DISTINCT COUNT(EmpType) =
SELECT DISTINCT 4 = 4, not 3. So that won't do.
Thanks.
-David
On Tuesday, April 03, 2001 8:01 PM, Michael Caulfield
[SMTP:[EMAIL PROTECTED]] wrote:
> SELECT COUNT (DISTINCT *) may not be legal, but in t-sql at least, SELECT
> DISTINCT COUNT(*) is perfectly legal (I'm not sure if it's standard SQL-92
> or not).
>
> So you could first check for SELECT DISTINCT and if that exists, replace it
> with SELECT DISTINCT COUNT(*) [as whatever]
>
> Mike.
> -----Original Message-----
> From: David Shadovitz [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, April 03, 2001 9:35 PM
> To: CF-Talk
> Subject: Limit Query Results
>
>
> I'm looking for suggestions:
>
> My intranet app lets users construct a SQL query. I want to guard against
> queries which would swamp the system by returning too much data. My plan is
> to
> cap the number of rows that a query can return. Sure, a row may contain
> many
> fields or few, but it's probably a good enough measure of a query's size.
> Here's the methodology:
>
> * Make a copy of the user's SQL statement
> * In this copy, replace the SELECT clause with SELECT COUNT(*) AS NumRows
> * Execute this copy
> * If NumRows exceeds some threshold, tell the user that he must refine his
> query. Otherwise, go ahead and execute it.
>
> One problem I see is the DISTINCT keyword. If the user's query is SELECT
> DISTINCT ..., I don't know how to get a count of the number of rows. SELECT
> COUNT(DISTINCT *) AS NumRows is not valid syntax. Perhaps I'll just let
> DISTINCT queries go without a limit.
>
> Thanks.
> -David
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
