Kevin, I actually tried this earlier and it still gave me the same results...I think its because the CasacID can be a one - two relationship....I am tring to make it work as an embedded query so it will select the top iMonthlyEventID, grouped by iMonthlyEventID....nto sure if this is possible though...
--- Kevin Schmidt <[EMAIL PROTECTED]> wrote: > SELECT DISTINCT(CMME.iMonthlyEventID) > > :) > > Kevin > > > -----Original Message----- > From: Amanda Stern [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 09, 2002 9:18 AM > To: CF-Talk > Subject: SQL help - getting first rec of field???? > > Hello, > > I have been trying to figure out how to write this > SQL > stmt..I have it written but it needs one more > modification....I need the query to only pull the > first unique EventID... > > SELECT CMME.iMonthlyEventId as EventID, > CMME.iCMApptId as CMapptID, > cli.vClientFName + cli.vClientLName as > ClientName, > CMME.dMonthlyEventDate as Event Date, > cas.iCasacApptID as CasacID, > cas.dCasacDate as CasacDate, > emp.vEmpFName + emp.vEmpLNAme as > Case_Manager, > cd1.vcodedecode as eventtypeName > FROM CMMonthlyEvent CMME, codedecode cd1, > Client cli, employee emp, CasacAppt cas, > CasacApptRelationship rel > WHERE emp.iempId = CMME.iempId > AND cas.iClientID = cli.iClientID > AND cd1.icodedecodeId = CMME.cEventTypeId > AND CMME.iCMApptId = rel.iCMApptId > AND cas.iCasacApptID = rel.icasacapptid > order by CMME.iCMApptId, dmonthlyeventdate > > the partial results are: > > EventID CMapptID ....... > 60 1 > 60 1 > 75 1 > 75 1 > 80 3 > 80 3 > 80 3 > > I want the results to be.. > 60 1 > 75 1 > 80 3 > > > > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > > ______________________________________________________________________ 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
