I want a select to give me id’s that are not in a consecutively order

oracleorder-byselect

I have an Oracle db with a table like this:

+----+-------+
| ID | INDEX |
+----+-------+
| 1  |   0   |
| 1  |   1   |
| 2  |   4   |
| 2  |   5   |
| 2  |   6   |
| 3  |   0   |
| 3  |   1   |
| 3  |   5   |
| 3  |   6   |
| 4  |   0   |
| 4  |   1   |
| 4  |   2   |
+----+-------+

I'd want a select to give me all the ID's with INDEX that does not start with 0 or are not in the correct order. From my example I'd want to get 2,3.
THANKS!

Best Answer

   create table index_test(id number, idx number);

   insert into index_test (id, idx) values (1,0);
   insert into index_test (id, idx) values (1,1);
   insert into index_test (id, idx) values (1,2);
   insert into index_test (id, idx) values (1,3);
   insert into index_test (id, idx) values (1,4);
   insert into index_test (id, idx) values (2,0);
   insert into index_test (id, idx) values (2,1);
   insert into index_test (id, idx) values (2,2);
   insert into index_test (id, idx) values (2,4);
   insert into index_test (id, idx) values (2,5);
   insert into index_test (id, idx) values (3,1);
   insert into index_test (id, idx) values (3,2);
   insert into index_test (id, idx) values (3,3);
   insert into index_test (id, idx) values (3,4);
   insert into index_test (id, idx) values (3,5);
   insert into index_test (id, idx) values (3,6);

select id from index_test group by id having max(idx)+1!=count(*) or min(idx)!=0;
Related Question