Multiple choice technology databases

Which of the following queries are correct to delete duplicate records in a table?

  1. SELECT * FROM table a WHERE ROWID < (SELECT MAX(ROWID) FROM table b WHERE a.col1 = b.col1);

  2. SELECT * FROM table a WHERE ROWID <= (SELECT MAX(ROWID) FROM table b WHERE a.col1 = b.col1);

  3. SELECT * FROM table a WHERE ROWID > (SELECT MIN(ROWID) FROM table b WHERE a.col1 = b.col1);

  4. SELECT * FROM table a WHERE ROWID >= (SELECT MIN(ROWID) FROM table b WHERE a.col1 = b.col1);
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

To delete duplicate records while keeping one copy, use ROWID comparisons. Options A and C identify duplicates by matching columns (col1) and then keeping records with ROWID less than the MAX or greater than the MIN respectively. Options B and D incorrectly use <= and >=, which would delete all duplicates including the one you want to keep. The subquery finds the group, and the outer query filters based on ROWID position.

Keep practicing — related questions