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 > > > > > > -----Original Message----- > From: stas [mailto:[EMAIL PROTECTED]] > Sent: Friday, April 05, 2002 3:32 PM > To: CF-Talk > Subject: Re: Query help > > What I am looking to do would be like this in CF: > > <cfquery = "getNums"> > SELECT DISTINCT number FROM table > </cfquery> > > <cfloop query = "getNums"> > <cfquery = "getNums"> > SELECT TOP 2 * > FROM table > WHERE number = #getNums.number# > </cfquery> > </cfloop> > > Is there anyway to combine this into one query? > > T-SQL maybe? > > > ----- Original Message ----- > From: "Cantrell, Adam" <[EMAIL PROTECTED]> > > > > And actually, if you DO want duplicate rows - use UNION ALL > on the same > table: > > (SELECT DISTINCT table1.field AS field1 FROM TABLE AS table1) > UNION ALL > (SELECT DISTINCT table2.field AS field1 FROM TABLE AS table2) > ORDER BY field1 > > Not sure if your db will handle it the same. > > > Adam. > > > > -----Original Message----- > > From: Cantrell, Adam [mailto:[EMAIL PROTECTED]] > > > > > > Output two rows or two columns? > > > > I don't know about rows, but columns would be fairly easy: > > > > SELECT DISTINCT > > field AS field1, > > field AS field2 > > FROM TABLE WHERE 0=0 > > > > Adam. > > > > > > > -----Original Message----- > > > From: stas [mailto:[EMAIL PROTECTED]] > > > > > > > > > > Hello, > > > > > > I have a table one column of which is a foreign key. For each > > > unique value > > > in this column I'd like to output 2 rows containing this > > > value. Obviously, > > > that would be pretty easy in CF, but I need it to be > > > completely in SQL. > > > Thanks for any tips! > > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm 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
