Query Help, urgent

[mkey]

Hi All,
I have to query a data that look something like this:

col_a col_b col_c
1 10 200
2 10 201
3 10 202
4 10 205
1 20 100
2 20 101

Now col_c should always increament by one. In row 5 the the value jumped to 205. There for its an error and I want to output that error with col_a and col_b to the user.
Once the col_b changed to 20 I have to check the col_c again to see if the values are incrementing by one again. In this case col_b is 20 and its fine(100, 101). I also have to also should do an order by on the col_a. Then I have to check the col_c. Do I need to write a procedure or can I do this in a script? The table in concern is pretty big.
My query should return:
col_a col_b col_c
4 10 205


Any thoughts will be greatly appreciated.

 

[rcurva]

Can you try this:

SELECT a.*
FROM table1 a, table1 b
WHERE a.col_a = (b.col_a - 1)
AND a.col_b = b.col_b
AND (a.col_c - b.col_c) > 1;

Based on your example, I came up with this straightforward approach... not unless you have other requirements.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[mkey]

Hi Robbie,
Thank you for your thoughts.
I tryed your script but its not returning any rows.

 

[mkey]

HI,
When I change the script to this I am getting this answer.
data:
col_a col_b col_c
3 13127 100
1 13127 101
4 13127 102
10 13127 104
1 13128 100
5 13128 101
6 13128 102

SELECT a.*
FROM table_x a, table_x b
WHERE
a.col_b = b.col_b
AND (a.col_c - b.col_c) > 1;

Result:
col_a col_b col_c
4 13127 102
10 13127 104
10 13127 104
10 13127 104
6 13128 102

As u can see the query is repeating more than once. Can you tell why?
Many thanks.

 

[rcurva]

Ooops, how abt, this?

SELECT c.*
FROM table1 c
WHERE (c.col_a, c.col_b, c.col_c) NOT IN
(SELECT a.col_a, a.col_b, a.col_c
FROM table1 a, table1 b
WHERE a.col_c = (b.col_c - 1)
AND a.col_b = b.col_b);


Same assumptions applied. Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sanjaynahar]

Try this out ...

SELECT a.*
FROM table1 a
WHERE 0 = ( SELECT COUNT(*) FROM table1 b
WHERE a.col_b = b.col_b
AND a.col_c = b.col_c + 1 )
AND a.col_a > 1

 

[KALYANIDRV]

Hi all,
The following query is not depending on id. It is the dynamic one. Please take this.

SELECT a.col_a,a.col_b,a.col_c
FROM table1 a
WHERE nvl(a.col_c - (SELECT max(b.col_c) col_c
FROM table1 b
WHERE b.col_c < a.col_c
AND b.col_b = a.col_b),0) > 1

please try this and let me know on the same.

Please contact me at ravich_74@hotmail.com

Thanks,
Ravi.