Home » SQL & PL/SQL » SQL & PL/SQL » duplicate values
duplicate values [message #219340] Wed, 14 February 2007 01:26 Go to next message
vdsk
Messages: 41
Registered: February 2006
Location: India & UAE
Member
I have a table having data like :
ColumA ColumnB COlumC
X 5 8
X 7 9
Y 1 3
I want to list all rows where
there is an overlap of value ranges. In this case first 2
where 7 is a value between 5 and 8. Pl tell a logic or PL/SQL
or any inbuit function. I am using Ora 10g..
Re: duplicate values [message #219343 is a reply to message #219340] Wed, 14 February 2007 01:49 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
A few comments
1) it is not clear whether one has to look only on previous rows where columna is the same (as it is written now)
2) create table and insert scripts you could give yourself, it would be faster for someone else to write just select query.

SQL> create table t (columna varchar2(1), columnb number, columnc number);

Table created.

SQL> insert into t values ('x', 5, 8);

1 row created.

SQL> insert into t values ('x', 7, 9);

1 row created.

SQL> insert into t values ('y', 1, 3);

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  select columna, columnb, columnc from (
  2    select t.*,
  3      lag(columnb) over (partition by columna order by columnb, columnc) prevb,
  4      lag(columnc) over (partition by columna order by columnb, columnc) prevc
  5    from t
  6  )
  7* where columnb between prevb and prevc
SQL> /

C    COLUMNB    COLUMNC
- ---------- ----------
x          7          9

SQL> 


yea and it seems you wanted both rows so here it is:

SQL> ed
Wrote file afiedt.buf

  1  select columna, columnb, columnc from (
  2    select t.*,
  3      lag(columnb) over (partition by columna order by columnb, columnc) prevb,
  4      lag(columnc) over (partition by columna order by columnb, columnc) prevc,
  5      lead(columnb) over (partition by columna order by columnb, columnc) nextb
  6    from t
  7  )
  8  where columnb between prevb and prevc or
  9*   nextb between columnb and columnc
SQL> /

C    COLUMNB    COLUMNC
- ---------- ----------
x          5          8
x          7          9

Gints Plivna
http://www.gplivna.eu

[Updated on: Wed, 14 February 2007 01:54]

Report message to a moderator

Re: duplicate values [message #219345 is a reply to message #219343] Wed, 14 February 2007 01:56 Go to previous messageGo to next message
vdsk
Messages: 41
Registered: February 2006
Location: India & UAE
Member
Gints..
thank u..but as I am new to this partition , pl tell how to include both the first two rows. ie it will show where are the
overlaps...
Re: duplicate values [message #219346 is a reply to message #219345] Wed, 14 February 2007 02:04 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
I've already corrected my first reply.

And here you can read more about analytic functions http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1007779

Gints Plivna
http://www.gplivna.eu
Re: duplicate values [message #219353 is a reply to message #219346] Wed, 14 February 2007 02:21 Go to previous messageGo to next message
vdsk
Messages: 41
Registered: February 2006
Location: India & UAE
Member
Gints
thanx for ur valuable time & link...
just a last one tip plz..
suppose I have diffrent values in columna ..eg
what change to make in script if it is like :-
SQL> select * from t;

C COLUMNB COLUMNC
- --------- ---------
x 5 8
y 7 9
z 1 3
Re: duplicate values [message #219394 is a reply to message #219353] Wed, 14 February 2007 04:14 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Instead of
partition by columna order by columnb, columnc

write
order by columna, columnb, columnc


But you really need to look at analytic functions. They will make your life easier.

Gints Plivna
http://www.gplivna.eu
Previous Topic: Granting privileges on trigger
Next Topic: passing in nested (multi-dimensional) varray to procedure?
Goto Forum:
  


Current Time: Sun Dec 11 03:58:22 CST 2016

Total time taken to generate the page: 0.11031 seconds