I tried that. That will give you records with com1 showing up more than once since the names ect are not distinct sql returns all the combinations. If it would only give me one per change in com1 I would be done.
Jeremy -----Original Message----- From: Snake [mailto:[EMAIL PROTECTED] Sent: Monday, May 22, 2006 8:52 AM To: CF-Talk Subject: RE: Possible in SQL? I would have thought distinct would do what you want, are you sure you use dit correctly. Select distinct com1, gender, fname, lname FROM mytable WHERE gender = 'M' Group by com1, gender, fname, lname - Snake -----Original Message----- From: Jeremy Bunton [mailto:[EMAIL PROTECTED] Sent: 22 May 2006 13:34 To: CF-Talk Subject: Possible in SQL? Hello, I have a table that looks something like this. Com1 gender fname lname 001 M Matt Smith 001 M Bill Wright 005 F Kara Brown 005 M Mike White What I need to do is pull a record from each "com1" group that meets my where criteria. Say from above I want all the males. I would want the answer to be 001 M Matt Smith 005 M Mike White Or 001 M Bill Wright 005 M Mike White I don't care which record I just want ONE per com1 id meeting the criteria. I tried using distinct but that will not work as you have to list all the field names out and then group but the none distinct ones which gives me all the com1's meeting the where criteria. Any idea's? Jeremy ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241141 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
