0

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!

4
  • What do you mean buy 2,3? ID 2 with 3 indexes?
    – Marco
    Aug 21, 2015 at 14:07
  • 1
    He means ids 2 and 3 since both violate the rule for the indices
    – QuadrExAtt
    Aug 21, 2015 at 14:11
  • Do not think that is what he means. 1,3, and 4 start with 0 so they are not valid. Only 2 does not start with 0 and 4, 5, and 6 are consecutive.
    – Marco
    Aug 21, 2015 at 14:13
  • 2 does not start with 0 and 3 does not have idx consecutives, 1 and 4 are ok. Thanks!
    – WDrgn
    Aug 21, 2015 at 14:18

3 Answers 3

2
   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;
7
  • In the example ID 2 has 4,5, and 6 and it is expected in the outcome.
    – Marco
    Aug 21, 2015 at 13:58
  • 1
    Yes. Hence min(idx)!=0 which means it shows up
    – QuadrExAtt
    Aug 21, 2015 at 14:02
  • What if you have only index 4 and 6? Is this acceptable?
    – Marco
    Aug 21, 2015 at 14:06
  • 1
    No. It neither starts with 0 nor is it consequtive
    – QuadrExAtt
    Aug 21, 2015 at 14:08
  • ID 2 with 4,6, and 6 is not returned by your solution. max(idx) is 6 and count(*) is only 3.
    – Marco
    Aug 21, 2015 at 14:10
2

Try:

select   id
from     tab
where    id not in (select id
                    from   tab
                    where  index=0)
group by id
having   max(index)+1!=count(*)
order by id;

Preventing that the combination of id and index is unique

0
-1
    DECLARE @Line TABLE(Id INT)


INSERT INTO @Line (Id) VALUES (1)
INSERT INTO @Line (Id) VALUES (2)
INSERT INTO @Line (Id) VALUES (3)
INSERT INTO @Line (Id) VALUES (4)
INSERT INTO @Line (Id) VALUES (5)
INSERT INTO @Line (Id) VALUES (7)
INSERT INTO @Line (Id) VALUES (9)




;WITH CTE as 
(
SELECT ROW_NUMBER() OVER    (ORDER BY Id) as RowId, Id FROM @Line
)
SELECT * FROM CTE where RowId <> Id

Hope it helps,

If its repeating then you can use rank or partition by
Maulin.

1
  • This syntax isn't going to work on Oracle. Aug 21, 2015 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.