Deleting duplicate rows based on timestamps
Hello..
I'm quite new to MYSQL.. I have a table with two fields : tag_in and time_in, where time_in is a timestamp field..
Some rows have the same value for tag_in.. I'd like to neaten up the database in 2 ways:
1. Delete rows with duplicate tag_in but removing the one with the oldest timestamp value..
2. Delete rows with duplicate tag_in but removing the one with the latest timestamp value..
How can i go about this ? Thank you for your help..
Software/Hardware used:
Software/Hardware used:
ASKED:
October 22, 2005 1:11 PM
UPDATED:
October 22, 2005 3:55 PM
Answer Wiki:
I'll show you the SQL to do this below, but first I have to explain something. During any two separate SQL executions, each timestamp will have a different value. For example, (examples shown in DB2 syntax):
INSERT INTO xxx VALUES(CURRENT TIMESTAMP);
INSERT INTO xxx VALUES(CURRENT TIMESTAMP);
The two timestamps will have different values, even if executed within the same clock tick. Any number of uses of timestamp within the same execution will have the same value. For instance,
INSERT INTO xxx
SELECT CURRENT TIMESTAMP
FROM yyy;
No matter how long this query takes to run, all the timestamps will be the same. Now, that said, here's how to eliminate duplicates, dropping the older ones:
DELETE FROM myTable a
WHERE EXISTS(
SELECT 1
FROM myTable b
WHERE a.matchCriteria=b.matchCriteria AND
a.timestampField<b.timestampField);
However, this doesn't eliminate the case of two records having been written in the same statement. There, position in the file may be more important to you. You can handle all cases as follows:
DELETE FROM myTable a
WHERE EXISTS(
SELECT 1
FROM myTable b
WHERE a.matchCriteria=b.matchCriteria AND
RRN(a)<RRN(b));
---
Sheldon Linker
Linker Systems, Inc.
http://linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904 from outside of North America
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
