Nice one Marian. Adam.
> -----Original Message----- > From: Marian Dumitrascu [mailto:[EMAIL PROTECTED]] > Sent: Friday, April 05, 2002 3:49 PM > To: CF-Talk > Subject: RE: Query help > > > try this , (but you'll need an unique id): > > select * from table t1 > where > t1.id in (select top 1 id from table t2 where t2.number = t1.number) > > union > > select * from table t4 > where > t4.id in (select top 1 id from table t3 where t3.number = > t4.number and > t3.id not in (select top 1 id from table t2 where t2.number > = t4.number)) > > order by number > > > > -Marian :) > > > > > > -----Original Message----- > > From: stas [mailto:[EMAIL PROTECTED]] > > Sent: Friday, April 05, 2002 4:26 PM > > To: CF-Talk > > Subject: Re: Query help > > > > > > Andrew - the query you posted using UNION SOLUTION works > when you only > > select one field per table, however, if you add more fields (as I > > need to), > > the output looks like a Cartesian. Any other ideas? Thanks! > > > > > > ----- Original Message ----- > > From: "Cantrell, Adam" <[EMAIL PROTECTED]> > > > > > > Hehe, I was going to post the same thing, but I caught > myself. That will > > only return two records - not two records per each unique > > columnXYZ. Off the > > top of my head, I can't think of a correct answer - it will > come to me, or > > someone else will post it before I figure it out. > > > > Adam. > > > > > > > > > -----Original Message----- > > > From: Matthew R. Small [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, April 05, 2002 2:53 PM > > > To: CF-Talk > > > Subject: RE: Query help > > > > > > > > > select top 2 * from table > > > where number in > > > (select distinct number from table) > > > > > > - Matt Small > > > > > > > > > ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
