Michael,
You are getting it, and I appreciate it.
My app uses an Oracle database, but here at home all I have is MS Access. In
MS Access, "select count(distinct type) from ..." yields a syntax error. I'll
see how Oracle handles it tomorrow.
-David
On Tuesday, April 03, 2001 9:55 PM, Michael Caulfield
[SMTP:[EMAIL PROTECTED]] wrote:
> Sorry --- i misread you the first time, thought you were asking something
> else (it's late)
>
> But if I get what you're asking now, I'm confused why it's a problem. For
> me, using the sample pubs directory in SQL2000:
>
> use pubs
> select count(distinct type) from dbo.titles
>
> gives me a number equal to the number of rows that
>
> use pubs
> select distinct type from dbo.titles
>
> would return. Am I still not getting this?
>
> -----Original Message-----
> From: David Shadovitz [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, April 03, 2001 11:23 PM
> To: CF-Talk
> Subject: RE: Limit Query Results
>
>
> 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
