comparing specific rows
-
Is there any way of comparing specific rows in a table? i.e. I want to delete one of two rows in a table (that also has more rows in it other than the 2 i want to compare). Basicaly, each of the rows have a date field and a unique idtntifier, I want to compare two rows against each other using the unique indetifier and delete the one with the earliest date.
Anyone know how this can be done?
appreciated.........
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
You can use the following query.
DELETE FROM MyTable
WHERE Unique_ID = <ID to Delete>
AND DateField = (SELECT min(t1.DateField)
FROM MyTable t1
WHERE t1.Unique_ID = <ID to Delete>) -
Hi,
you could set rowcount to 1
and then delete based on the unique identifier, ordered by date. This was delete the earlier one and leave the later one. (don't forget to set it back though !)
Paul
-
An alternative to delete all those records is
DELETE MyTable
FROM MyTable t1 INNER JOIN MyTable t2
ON t1.Unique_ID = t2.Unique_ID
AND t1.DateField < t2.DateFieldThis will delete all the records for a single Unique_ID, except for the one with the latest date.
-
This is how I do it but I do sugegst follow using the TRANSACTION method to be able to recover if you find an error in the data.
BEGIN TRANSACTION
delete
tblname
where
exists (
select
*
from
tblName oq
inner join
(
select
iq.uniind mxid,
max(iq.datefld) as mxcd
from
tblName iq
group by
iq.uniind
) as MaxCD
on
oq.uniind = MaxCD.mxid and
oq.datefld != MaxCD.mxcd
where
oq.uniind = tblname.uniind AND
oq.datefld = tblname.datefld
)Validate your data was deleted correctly and do
COMMIT TRANSACTION
otherwise do
ROLLBACK TRANSACTION
to recover
Also, first time I suggest use just one to test so add
AND
tblname.uniind = 'testuniindvalue'to the above code.
-
What is 't1'??
DELETE MyTable
FROM MyTable t1 INNER JOIN MyTable t2
ON t1.Unique_ID = t2.Unique_ID
AND t1.DateField < t2.DateField
[/code]
This will delete all the records for a single Unique_ID, except for the one with the latest date.
[/quote]
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
t1 and t2 are aliases for mytable1 and mytable2
-
there is only one table.............
[/quote]
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
Hi Andrew,
quote:
there is only one table.............
A table can be joined to itself.
Take a look at self-join in BOL.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
sorry, bit too quick there, it's s self join. This effectively is two copies of the base table so you can do something like
"AND t1.DateField < t2.DateField"
-
actually, just read up on that..thanks guys.. ill give it a go and it looks like it should work..appreciate it
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
im getting an error whn i execute the query.......'tablename is ambiguous'
any ideas why?
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
Which code are you running?
-
DELETE MyTableFROM MyTable t1 INNER JOIN MyTable t2 ON t1.Unique_ID = t2.Unique_ID AND t1.DateField < t2.DateField
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
Hi Andrew,
quote:
DELETE MyTable FROM MyTable t1 INNER JOIN MyTable t2 ON t1.Unique_ID = t2.Unique_ID AND t1.DateField < t2.DateField
because you have given two aliases for MyTables, you should change DELETE MyTable to t1.
Should work
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply