Deleting duplicate rows based on timestamps

0 pts.
Tags:
Database
DB2
Oracle
SQL
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..
ASKED: October 22, 2005  1:11 PM
UPDATED: October 22, 2005  3:55 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Amateur2005
    [...] Original Address: http://itknowledgeexchange.techtarget.com/itanswers/deleting-duplicate-rows-based-on-timestamps/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following