Selectively ignoring tuples from a table

Question

The problem that we are tackling with a data mining application is best described with an illustrative example.

There is a sample table myTable, which is defined as follows:

CREATE TABLE myTable
(
  id        INT UNSIGNED AUTO_INCREMENT,
  colA      VARCHAR(8),
  colB      VARCHAR(12),
  revFlag   CHAR(8), -- 'REVISED' or any other value, including NULL

  PRIMARY KEY(id)
);  

Any tuple with a revFlag value of REVISED takes precedence over any other tuple with the same value for colA, as long as the revFlag value of the latter tuple is not REVISED. In other words when we select rows from the table we skip all rows for which the revFlag value is not REVISED and there exists a row with the same value for colA for which the revFlag value is REVISED.

We populate the table as follows:

INSERT INTO myTable(colA, colB) VALUES ('XSR0KA3V', 'OLD-O7RAR81X'),
('4F2JG71O', 'OLD-E71BE63L'), ('MML3HN48', 'OLD-B02PFB63'),
('5H0MWVSB', 'OLD-V70XLGHT'), ('JW73ZX0J', 'OLD-KME1GXQF'),
('XZV0EY0G', 'OLD-N06BURDF'), ('9HBQZ88V', 'OLD-76HSPUAL'),
('YI5AT6G4', 'OLD-X8KAWD7Z');


INSERT INTO myTable(colA, colB, revFlag) VALUES
('XSR0KA3V', 'NEW-O7RAR81X', 'REVISED'),
('MML3HN48', 'NEW-B02PFB63', 'REVISED'),
('9HBQZ88V', 'NEW-76HSPUAL', 'REVISED'),
('YI5AT6G4', 'NEW-X8KAWD7Z', 'XYZ'),
('Z8H2B5KY', '3RINJV0K', 'REVISED');

Naturally SELECT * FROM myTable yields the following:

+----+----------+--------------+---------+
| id | colA     | colB         | revFlag |
+----+----------+--------------+---------+
|  1 | XSR0KA3V | OLD-O7RAR81X | NULL    |
|  2 | 4F2JG71O | OLD-E71BE63L | NULL    |
|  3 | MML3HN48 | OLD-B02PFB63 | NULL    |
|  4 | 5H0MWVSB | OLD-V70XLGHT | NULL    |
|  5 | JW73ZX0J | OLD-KME1GXQF | NULL    |
|  6 | XZV0EY0G | OLD-N06BURDF | NULL    |
|  7 | 9HBQZ88V | OLD-76HSPUAL | NULL    |
|  8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL    |
|  9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ     |
| 13 | Z8H2B5KY | 3RINJV0K     | REVISED |
+----+----------+--------------+---------+

---

We would like to design a query that does not return any tuples that are REVISED by other tuples. In our case the output should look like this:

+----+----------+--------------+---------+
| id | colA     | colB         | revFlag |
+----+----------+--------------+---------+
|  2 | 4F2JG71O | OLD-E71BE63L | NULL    |
|  4 | 5H0MWVSB | OLD-V70XLGHT | NULL    |
|  5 | JW73ZX0J | OLD-KME1GXQF | NULL    |
|  6 | XZV0EY0G | OLD-N06BURDF | NULL    |
|  8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL    |
|  9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ     |
| 13 | Z8H2B5KY | 3RINJV0K     | REVISED |
+----+----------+--------------+---------+

Answer 1

You can accomplish this with a window function like the ROW_NUMBER() function inside of a subquery or CTE like so:

SELECT id, colA, colB, revFlag
FROM
(
    SELECT id, colA, colB, revFlag, 
        ROW_NUMBER() OVER 
        (
            PARTITION BY colA 
            ORDER BY CASE WHEN revFlag = 'REVISED' THEN 1 ELSE 0 END DESC, id DESC
        ) AS SortOrder -- Generates a unique ID for each row of the same value of colA sorted by revFlag = 'REVISED' first then by id descending
    FROM myTable
) myTableSorted
WHERE SortOrder = 1

As per my comment, the SortOrder field is generated by partitioning on colA and within that partition generating a unique incremental id that is sorted with the revFlag = 'REVISED' having priority first, and then by the myTable.id column descending (to get the latest row per colA).