Remove dupes from recordset excluding columns from dupe condition.

Question

I'm up against a mssql database, having a SQL query like...

SELECT id, type, start, stop, one, two, three, four
FROM a
UNION ALL
SELECT id, type, start, stop, one, two, three, four
FROM b
UNION ALL
SELECT id, type, start, stop, one, two, three, four
FROM c
ORDER BY type ASC

Resulting in...

row |  id  type  start       stop         one   two    three   four
----+--------------------------------------------------------------
 1  |  1   a     2010-01-01  2010-01-31   100   1000   1000    100
 2  |  1   a     2010-02-01  2010-12-31   100   500    500     50
 3  |  1   b     2010-01-01  2010-01-31   100   NULL   NULL    100
 4  |  1   b     2010-01-01  2010-12-31   100   NULL   NULL    100
 5  |  1   c     2010-01-01  2010-01-31   0     NULL   NULL    100
 6  |  1   c     2010-01-01  2010-12-31   0     NULL   NULL    100

However, I would much rather prefer the following outcome...

row |  id  type  start       stop         one   two    three   four
----+--------------------------------------------------------------
 1  |  1   a     2010-01-01  2010-01-31   100   1000   1000    100
 2  |  1   a     2010-02-01  2010-12-31   100   500    500     50
 4  |  1   b     2010-01-01  2010-12-31   100   NULL   NULL    100
 6  |  1   c     2010-01-01  2010-12-31   0     NULL   NULL    100

That is, eliminating row 3 and 5, since they are dupes to row 4 and 6 in every way but the stop-column, AND whereas the unfortunate row having to lowest value in the excluding stop-column is to be removed.

How can I accomplish this? I've been thinking something like...

SELECT * FROM (
    SELECT id, type, start, stop, one, two, three, four
    FROM a
    UNION ALL
    SELECT id, type, start, stop, one, two, three, four
    FROM b
    UNION ALL
    SELECT id, type, start, stop, one, two, three, four
    FROM c
    ORDER BY type ASC
) AS types
GROUP BY ... HAVING ???

I need guidance, please help.

(And no, I'm in no position to change any conditions, I've got to work with the given situation.)

Answer 1

This should work:

SELECT
     id,
     type,
     start,
     stop,
     one,
     two,
     three,
     four
FROM
     A T1
LEFT OUTER JOIN A T2 ON
     T2.id = T1.id AND
     T2.type = T1.type AND
     T2.start = T1.start AND
     T2.one = T1.one AND
     ...
     T2.stop > T1.stop
WHERE
     T2.id IS NULL     -- This must be a NOT NULL column for this to work

This assumes that the type column is the same value as the table names as in your examples. If you might have duplicate rows between tables then you'll need to do this same logic using a subquery of what you have instead of A. If my assumption is correct, then just replace each of your three UNION ALL queries with the above, changing the table names.

The idea is that if a row exists that matches, but with a later stop date then you don't want to include the row in the results. Using the LEFT OUTER JOIN, the only way that T2.id would be NULL is if there were no such match, so we can include it in the result set (that's why id has to be a NOT NULL column for this to work.)

Since you said that you can't change the DB I'll spare you the, "this design sucks" reprimand ;)

Answer 2

Similar questions have been asked and answered. For example: http://stackoverflow.com/questions/1769931/select-uniques-and-one-of-the-doubles

And your situation is even simpler (if I understood your problem description correctly):

select id, type, start, max(stop), one, two, three, four
    from (...) types
    group by id, type, start, one, two, three, four
    order by ...

In place of (...) you put your selects from a, b and c. Just leave out order by clause.

Or, if instead of (id, type, start)->(one, two, three, four) you have (id, type, start, stop)->(one, two, three, four) (meaning you have to chose other columns that correspond to max(stop)), this query usually results in sensible execution plan:

select id, type, start, stop, one, two, three, four
    from (...) types
    where stop = (select max(stop)
                  from (...) t2
                  where t2.id = types.id
                        and t2.type = types.type
                        and t2.start = types.start)

but it depends on how data is distributed among your source tables and what indexes are present. In some cases solutions from link above might still be better.