Walter Conti wrote: > <cfquery name="getSigs" datasource="firme"> > select signerName, signerLocation, signerComment, dateSigned, > MIN([dateSigned]) as startDate, MAX([dateSigned]) as endDate > from signatures > where signatures.petId ='#id#' > group by signerName, signerLocation, signerComment, dateSigned > order by dateSigned desc > </cfquery> > > returns the same (MAX) date for MIN and MAX even though the date range is > ample. > The error is laughing at me but I cannot see it. > Thanks for helping. > You are grouping by the dateSigned column. The MIN and MAX will be on a group defined with that column...so in each row dateSigned = MIN(dateSigned) = MAX(dateSigned). If you want to return the individual dateSigned values and the related MIN and MAX for that id in each row, you will probably have to do it with subqueries...something like...
SELECT signerName, signerLocation, signerComment, dateSigned, (SELECT MIN([dateSigned]) FROM signatures WHERE petId = '#id#') AS startDate, (SELECT MAX([dateSigned]) FROM signatures WHERE petId = '#id#') AS endDate FROM signatures WHERE signatures.petId ='#id#' ORDER BY dateSigned desc ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257706 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
