RE: Possible in SQL?
> I have a table that looks something like this. > > Com1 genderfname lname > 001 M Matt Smith > 001 M Bill Wright > 005 FKara 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
RE: Possible in SQL?
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 genderfname lname 001 M Matt Smith 001 M Bill Wright 005 FKara 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
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 genderfname lname 001 M Matt Smith 001 M Bill Wright 005 FKara 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:241123 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
Re: Possible in SQL?
fixing my query...I had used used foo as the tablename originally, and not changed it in both places... select * from sometable a WHERE a.gender = 'M' AND lname = (SELECT max(lname) FROM sometable WHERE com1=a.com1) -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241122 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
Re: Possible in SQL?
You could do it with a subquery select * from sometable a WHERE a.gender = 'M' AND lname = (SELECT max(lname) FROM foo WHERE com1=a.com1) (of course, this assumes that lname is unique within a com1, which may not be the case...you may need to add on a identity field to make sure you only get one value for each com1) -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241121 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