for the record this is how it's working now, thanks for your help Bryan, i
used some of your ideas and structured them differently
SELECT *
FROM Highlights
WHERE Highlights.Status = 1 AND Highlights.HighlightID IN (SELECT DISTINCT
highlightDates.highlightID FROM HighlightDates WHERE
HighlightDates.HighlightDate >= #startweek# AND HighlightDates.highlightdate
<= #DateAdd('ww', 1, startweek)#)
=)
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 November 2001 12:40 p.m.
To: CF-Talk
Subject: RE: SQL Query
your assumption is correct. When using distinct it it returns all distinct
RECORDS. A record consists of everything in the SELECT clause.
You have two real options here:
1. If you have a beefy CF server and there aren't too many rows in the db
with the same hightlightID then try this: remove the DISTINCT clause, add
'ORDER BY highlightID' and then use <CFOUTPUT QUERY="" GROUP="highlightID">.
This will output a row to the page every time a new distinct highlightID is
encountered in the loop.
2. If you have a weak CF server and a beefy Enterprise level DB then do
this (Oracle version here):
|SELECT hd.highlightID,
|hd.HighlightDate,
|h.*
|FROM Highlights h, HighlightDates hd
|WHERE hd.HighlightDate >= #startweek# AND
|hd.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND h.HighlightID = hd.HighlightID AND
|h.Status = 1 AND
hd.rowID IN (select min(rowID) from highlightdates group by highlightID)
In the above example you'll notice I used rowID - this is Oracle's way of
internally identifying rows and it always exists. The MIN() function will
grab the first row that was inserted for each group of similar highlightIDs.
This is an inefficient query, but you don't have much choice - I would
recommend using the GROUP attribute from option 1 unless there are thousands
of rows.
Bryan Love Macromedia Certified Professional
Internet Application Developer / Database Analyst
Telecommunication Systems Inc.
[EMAIL PROTECTED]
"What father would hesitate to say 'if there must be trouble let it be in my
day, that my child may have peace'?"
- Thomas Paine, An American Crisis
-----Original Message-----
From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 3:15 PM
To: CF-Talk
Subject: RE: SQL Query
i broke them up into its columns, but i still get this message
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image
data type cannot be selected as DISTINCT.
-----Original Message-----
From: Sicular, Alexander [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 November 2001 11:59 a.m.
To: CF-Talk
Subject: RE: SQL Query
Try breaking up the Highlights.* into its columns.
Good luck,
Alexander Sicular
Chief Technology Architect
Neurological Institute of New York
Columbia University
as867 [at] columbia {dot} edu
|-----Original Message-----
|From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
|Sent: Tuesday, November 13, 2001 5:51 PM
|To: CF-Talk
|Subject: SQL Query
|
|
|SELECT DISTINCT highlightDates.highlightID,
|HighlightDates.HighlightDate,
|Highlights.*
|FROM Highlights, HighlightDates
|WHERE HighlightDates.HighlightDate >= #startweek# AND
|HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND Highlights.HighlightID = HighLightDates.HighlightID AND
|Highlights.Status = 1
|
|Can some one tell me why the query above doesn't work
|I thought it would only look at highlightDates.highlightID and
|select a distinct one of those, but it seems to be trying to
|do a distinct over all of the selected fields
|
|help! how can i make it select a distinct highlightID?
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
