Revision history [back]

It would depend on the specific database and table being used, but generally the query to retrieve all rows in which two IDs appear for the first time would involve using the DISTINCT and MIN functions to select the earliest occurrence of each ID, and then joining the results on a common ID field. For example, in MySQL syntax:

SELECT * FROM mytable WHERE id IN (SELECT DISTINCT MIN(id) FROM mytable GROUP BY id HAVING COUNT() = 1) OR other_id IN (SELECT DISTINCT MIN(other_id) FROM my_table GROUP BY other_id HAVING COUNT() = 1) JOIN (SELECT DISTINCT MIN(id) AS id1, MIN(otherid) AS otherid1 FROM mytable GROUP BY id, otherid HAVING COUNT(*) = 1) AS tmp ON mytable.id = tmp.id1 AND mytable.otherid = tmp.otherid1;

This query selects all rows where either the ID or otherID appears only once in the table (i.e. it is the first occurrence), and then joins them with all other rows where both the ID and otherID appear only once. The result is a table of all rows where both IDs appear for the first time.