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
