> 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?
You're not looking for distinct records, you want one random record from within a set of records that otherwise match your search conditions. You can fix this within your SQL using "TOP 1" or whatever similar functionality your database provides to get the first matching record within a set, or you can fix this within your CFML by using the GROUP attribute of CFOUTPUT to just show the first row within the set. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241145 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
