Patricia,
PK's are always distinct, so SELECT COUNT(PK) and SELECT COUNT (DISTINCT PK)
would be identical.
I shouldn't have used a "*" in my example; the trouble is with something like
SELECT DISTINCT EmpType, where EmpType isn't the PK.
Thanks.
-David
On Tuesday, April 03, 2001 7:51 PM, Patricia Lee [SMTP:[EMAIL PROTECTED]] wrote:
> IF:
> 1) all tables have primary Keys
> 2) You can programatically know which primary key is the correct one
> to use
>
> THEN:
>
> I beleive that Select count (Distinct PK) is correct syntax. I just tested
> it on a simple SQL 7 database and it worked....
>
>
>
> |-----Original Message-----
> |From: David Shadovitz [mailto:[EMAIL PROTECTED]]
> |Sent: Tuesday, April 03, 2001 10: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
