query help 2

[DotNetGnat]

Guys,

I have the following table:

ID | dist
_________
1 | 100.12
1 | 100.24
1 | 100.35
2 | 300.12
2 | 100.14
2 | 300.35
3 | 10.12
3 | 10.24

I want to find out the record within the particular ID whose dist(distance) is greater than 5

for the above data:

2 | 100.35 is way off..

thanks

-DNG

 

[SantaMufasa]

DNG,

Please post the code that you have tried so far. (It might also help us understand what you are trying to do. <grin>)

particular ID whose dist(distance) is greater than 5...for the above data:...2 | 100.35 is way off...

Given your sample data, I have no idea what the above means. Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

opps...that has to be 100.14...not 100.35

Thanks Dave, but i guess I did not explain it right...

I want to do a check on each record'distance with this preceding lesser distance to see if there are no weird jumps in the data with an ID.

For example:

1 | 10.1
1 | 10.2
1 | 10.3

has no problem

but if we look at the following

2 | 10.3
2 | 10.4
2 | 20.1
2 | 10.5

there is a problem with the third record because the difference between the second record's distance and third record's distance is greater than 5.

may be its a data entry problem...but because of that...even the fourth record with show up as a problem because we will be comparing with the third record's distance...

i am just trying to get a preliminary list of wrong data entries which will then be later checked manually...

-DNG

 

[DotNetGnat]

and I tried something like this:

Select a.dist from mytable a, mytable b
where a.id = b.id and A.dist < b.dist
and b.dist-a.dist > 5

-DNG

 

[SantaMufasa]

Much clearer now, DNG...Thanks.

We certainly can help you resolve this simply and easily, provided that you have one more piece of data (that I am hoping you have). It has to do with your ordinal references:

...third record...second record...forth record...third record....

For rows/records to have ordinal meanings, there must be something that determines that order, such as time, record number, et cetera. I don't see any designation for your rows that might determine order. And you cannot depend on Oracle to deliver rows in any particular order without an ORDER BY clause.


Once you provide some column/express that determines ORDER, we can resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

Thanks Dave...

This is how my table looks like now...

ID | OrderNum |dist
___________________
1 | 1 |100.12
1 | 2 |100.24
1 | 3 |100.35
2 | 1 |300.12
2 | 2 |100.14
2 | 3 |300.35
3 | 1 |10.12
3 | 2 |10.24

-DNG

 

[taupirho]

A classic analytic problem.

Try this as a starting point,

SQL> select * from tom;

ID DIST X
---------- ---------- ----------
1 100.12 1
1 100.24 2
1 100.35 3
2 300.12 1
2 100.14 2
2 300.25 3
3 10.12 1
3 10.24 2


1 select * from
2 (
3 select
4 id,
5 x,
6 dist,
7 lag(dist) over (partition by id order by x) prev_dist
8 from tom
9 )
10 where prev_dist is not null
11* and abs(prev_dist - dist) > 5
SQL> /

ID X DIST PREV_DIST
---------- ---------- ---------- ----------
2 2 100.14 300.12
2 3 300.25 100.14



In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

...And if you are not yet into Oracle Analytics, a solution that uses classical code is:

set heading on
col x heading "Current Distance" format a20
col y heading "Next Distance" format a20
col z heading "Difference"
Select a.id||'/'||a.seq||': '||a.dist x, b.id||'/'||b.seq||': '||b.dist y, abs(a.dist-b.dist) z
  from (select * from mytable order by id, seq) a, mytable b
 where a.id = b.id
   and abs(b.dist-a.dist) > 5
   and b.seq = (select min(seq) from mytable where seq > a.seq)
/

Current Distance     Next Distance        Difference
-------------------- -------------------- ----------
2/4: 300.12          2/5: 100.14              199.98
2/5: 100.14          2/6: 300.35              200.21

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

Thank you guys, I really appreciate your assistance.

Have Stars...

-DNG

 

[SantaMufasa]

Thanks, DNG!

BTW, I noticed on the re-read of my post that I had failed to rename my SEQ column (from my draft solution) to your ORDERNUM column (following your update).

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

Thanks again...and Yes I noticed that already and changed the query accordingly...

-DNG