You may want to try to keep coldfusion out of this process. If
there are a large number of records the CF solution could take a long
time. You said MSSQL right? Do it with a stored proc. If you need a hand
with it email me offlist.
Greg
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 4:48 PM
To: CF-Talk
Subject: (SOLVED) sql question
here is what I finished up with....
<cfquery name="get" datasource="tableOne">
select distinct reportId, Id from
testReports where date = {ts '2004-02-11 00:00:00'}
order by reportId
</cfquery>
<cfloop query="get">
<cfif get.currentRow mod 2>
<cfquery name="delete" datasource="tableOne">
delete from testReports
where Id = #get.Id#
</cfquery>
</cfif>
</cfloop>
-----Original Message-----
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 4:23 PM
To: CF-Talk
Subject: RE: sql question
You could write a stored procedure that does something like:
1. select all rows, order by the column with dupes 2.loop through the
rows, record the column's value at each iteration 3. if the current
value is equal to the last value, delete the current row.
Of course this will leave only one of each duplicate and you asked to
only have one of each duplicate removed. If you really want that, which
I doubt, just modify the proc to do:
1. select all rows, order by columns with dupes 2. loop through the
rows, record the column's value at each iteration. 3. if the current
value is equal to the last value, set a flag to indicate you're in a
duplicate 4. when the current value is not equal to the last value and
your flag is on, delete the last value's row
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 4:01 PM
To: CF-Talk
Subject: ot: sql question
hi there.
say I have a database, mssql.
and I know 1 column that should be unique, and actually I have
duplicates in there.
is there a query that I can write that will select them all, find the
duplicates and remove only 1 of the duplicate records?
thanks.
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
