0

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 |
+----+----------+--------------+---------+
3
  • I presume when there are two rows with the revFlag = 'REVISED' for the same value of colA, you'd want to take the row that is newer of the two? – J.D. Feb 28 at 22:34
  • @J.D. Although we have not seen a situation yet where there are multiple rows with the revFlag = 'REVISED' for the same value of colA, in such a situation we would go with the newest row (i.e. with the highest value of id) – Sandeep Mar 1 at 19:08
  • Please see my additional questions in the comments of my answer too. – J.D. Mar 1 at 19:17
0

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).

2
  • The query does not return {8, YI5AT6G4, OLD-X8KAWD7Z, NULL} – Sandeep Mar 1 at 2:39
  • @Sandeep Ah I did not notice that in your sample data. So in cases where the revFlag does not equal REVISED you want to return all rows with dupes of colA? What about the case where there is a row with revFlag = 'REVISED' and for the same colA value there's a newer row that came after where revFlag doesn't equal REVISED, would you expect both rows back or just the older REVISED row? – J.D. Mar 1 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.