if you need to do this in sql alone, the following would be what i would do using
cursors in sql2k...
hih
andres
declare cur01 cursor for
SELECT DISTINCT number as tbl_number FROM table
OPEN cur01
GO
DECLARE @tbl_number int
fetch next from cur01
into @tbl_number
while @@FETCH_STATUS = 0
begin
SELECT TOP 2 field1, field2, field3 (etc...)
FROM table
WHERE number = @tbl_number
end
CLOSE cur01
DEALLOCATE cur01
GO
-----Original Message-----
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 05, 2002 3: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!
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
