John A Meinel wrote: >SELECT t1.id+1 as id_new FROM id_test t1 > WHERE NOT EXISTS > (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) > ORDER BY t1.id LIMIT 1;
This works well on sparse data, as it only requires as many index access as it takes to find the first gap. The simpler "NOT IN" version that everybody seems to have posted the first time round has a reasonably constant (based on the number of rows, not gap position) startup time but the actual time spent searching for the gap is much lower. I guess the version you use depends on how sparse you expect the data to be. If you expect your query to have to search through more than half the table before finding the gap then you're better off using the "NOT IN" version, otherwise the "NOT EXISTS" version is faster -- on my system anyway. Hope that's interesting! Sam ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match