Delete maximum records in a table

0 pts.
Tags:
Database
DB2
Oracle
Hi! I need to Delete maximum records in a table that has 2 columns, such as Ord No. & Part No. Each Part No. can have many Ord Nos. I need to identify the Maximum Ord No per part No & delete the record from the table. Help would be much appreciated! Sarj
ASKED: November 15, 2005  11:29 PM
UPDATED: November 23, 2005  11:41 AM

Answer Wiki

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

Although there are a number of ways to do this in a single SQL statement, it’s best to use a cursor loop, since any such SQL statement would rely on order of execution, since it can be version-dependent. Here’s how I’d do it:

Assuming:

CREATE TABLE myTable(
major INTEGER,
minor INTEGER);

I’d code this:

long major, minor;

EXEC SQL DECLARE myCursor CURSOR AS
SELECT major,
MAX(minor)
FROM myTable
GROUP BY major;

EXEC SQL OPEN myCursor;

for (;;) {

EXEC SQL FETCH myCursor INTO :major, :minor;
if (SQLCODE)
break;

EXEC SQL DELETE FROM myTable
WHERE major=:major AND
minor=:minor;
}

EXEC SQL COMMIT;

Discuss This Question: 5  Replies

 
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
  • LeChuck
    Hi Sarj The solution to your problem of course depends on which DBMS you're using. This solution is written for MS SQL Server. The last select shows which data will be deleted, if you remove the comment-marks around the delete-statement! - This way you won't by accident delete the wrong data before having ensured, they are the correct data for deletion! Good luck :) Jacob SQL script: declare @tabMaxID table (MaxID int) insert into @tabMaxID (MaxID) select max(OrdID) from OrdNo group by PartID select * from OrdNo where exists ( select null from @tabMaxID maxid where maxid.MaxID = OrdNo.OrdID ) /* delete from OrdNo where exists ( select null from @tabMaxID maxid where maxid.MaxID = OrdNo.OrdID ) */ /* This solution needs OrdNo.OrdID (Primary Key of OrdNo) NOT to be of the datatype UniqueIdentifier, since you cannot use the max-function on that datatype. If that is the case, you'll need to create some work-around for that! */
    0 pointsBadges:
    report
  • DaveInAZ
    This sounds like a very strange need, from a business perspective, but if Sarj wants/needs to use LeChuck/Jacob's solution and he DOES have a UniqueIdentifier as the Primary Key, he could simply drop the key temporarily while executing this routine. It might take a few minutes to rebuild it afterwards, but that's life.
    0 pointsBadges:
    report
  • Quilly
    Provided each Part No. wont have the same Order No listed many times, this should do the job. Or maybe you don't even care if it does, you just want to remove each Part No.'s maximum Order No. DELETE FROM "your_table" WHERE "Part No." & "Ord No." IN ( SELECT "Part No." & Max("Ord No.") FROM "your_table" GROUP BY "Part No." ) Ampersand use is just for convenience, use what ever your RDBMS specifies for concatenation. Max() you may need to check if it's supported by your RDBMS. Shane.
    0 pointsBadges:
    report
  • 290861
    Hi! All Appreciate your input! What I got is an MS Access mdb. Could you please recode for Access. Sarj
    0 pointsBadges:
    report
  • DaveInAZ
    Quilly's solution will work in Access without recoding.
    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