HELP!! 1

[mkey]

HI ALL,
I have posted this same question before but I couldn't find my thread.
This is my data that I am trying to query.
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.

 

[THoey]

I don't understand what you are trying to do, but your original thread is:

thread185-152727 Terry M. Hoey

 

[mkey]

HI Terry,
Sorry for any confusion that I might have caused you.
What I am trying to do is that the col_c should increment by one everytime based on col_b.
So for example:
col_b col_c
13127 100
13127 101
13127 102
13127 105
13128 1000
13128 1001

So in this case I want the query to return 4th row where the col_c didn't increament by one. So the result I want to return is:
13127 105

I hope I didn't confuse you.
Thank you for all your help.
mkey

 

[jee]

Try this:

SELECT * FROM
test a
WHERE NOT EXISTS ( 
   SELECT col_a
   FROM test b
   WHERE a.col_b = b.col_b
   AND a.col_c = b.col_c + 1)
AND EXISTS (
   SELECT col_a
   FROM test b
   WHERE a.col_b = b.col_b
   AND a.col_c > b.col_c);