StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This query finds the number of duplicates:</p> <pre><code>select od1.object_id, od1.start_date, od1.end_date, sum(od2.id is not null) as dups from object_date od1 left join object_date od2 on od2.object_id = od1.object_id and od2.end_date >= od1.start_date and od2.start_date <= od1.end_date and od2.id != od1.id group by 1,2,3; </code></pre> <p>You can use this query as the basis of a query that gives you exactly what you asked for (see below for output).</p> <pre><code>select object_id, case dups when 0 then start_date else '<na>' end as start_date, case dups when 0 then end_date else '<na>' end as end_date, sum(dups) as dups from ( select od1.object_id, od1.start_date, od1.end_date, sum(od2.id is not null) as dups from object_date od1 left join object_date od2 on od2.object_id = od1.object_id and od2.end_date >= od1.start_date and od2.start_date <= od1.end_date and od2.id != od1.id group by 1,2,3) x group by 1,2,3; </code></pre> <p>Note that I have used an <code>id</code> column to distinguish the rows. However, you could replace the test of id's not matching with comparisons on every column, ie replace <code>od2.id != od1.id</code> with tests that every other column is not equal, but that would require a unique index on all the other columns to make sense, and having an id column is a good idea anyway.</p> <p>Here's a test using your data:</p> <pre><code>create table object_date ( id int primary key auto_increment, object_id int, start_date int, end_date int ); insert into object_date (object_id, start_date, end_date) values (1,1,5),(1,2,4),(1,6,8),(2,2,3); </code></pre> <p>Output of first query when run against this sample data:</p> <pre><code>+-----------+------------+----------+------+ | object_id | start_date | end_date | dups | +-----------+------------+----------+------+ | 1 | 1 | 5 | 1 | | 1 | 2 | 4 | 1 | | 1 | 6 | 8 | 0 | | 2 | 2 | 3 | 0 | +-----------+------------+----------+------+ </code></pre> <p>Output of second query when run against this sample data:</p> <pre><code>+-----------+------------+----------+------+ | object_id | start_date | end_date | dups | +-----------+------------+----------+------+ | 1 | 6 | 8 | 0 | | 1 | <na> | <na> | 2 | | 2 | 2 | 3 | 0 | +-----------+------------+----------+------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload