Say I have a table which I query like so:
select date, value from mytable order by date
and this gives me results:
date value
02/26/2009 14:03:39 1
02/26/2009 14:10:52 2 (a)
02/26/2009 14:27:49 2 (b)
02/26/2009 14:34:33 3
02/26/2009 14:48:29 2 (c)
02/26/2009 14:55:17 3
02/26/2009 14:59:28 4
I'm interested in the rows of this result set where the value is the same as the one in the previous or next row, like row b which has value=2 the same as row a. I don't care about rows like row c which has value=2 but does not come directly after a row with value=2. How can I query the table to give me all rows like a and b only? This is on Oracle, if it matters.
One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.
Check for Duplicates in Multiple Tables With INNER JOINUse the INNER JOIN function to find duplicates that exist in multiple tables. Sample syntax for an INNER JOIN function looks like this: SELECT column_name FROM table1 INNER JOIN table2 ON table1. column_name = table2.
Use the lead and lag analytic functions.
create table t3 (d number, v number);
insert into t3(d, v) values(1, 1);
insert into t3(d, v) values(2, 2);
insert into t3(d, v) values(3, 2);
insert into t3(d, v) values(4, 3);
insert into t3(d, v) values(5, 2);
insert into t3(d, v) values(6, 3);
insert into t3(d, v) values(7, 4);
select d, v, case when v in (prev, next) then '*' end match, prev, next from (
select
d,
v,
lag(v, 1) over (order by d) prev,
lead(v, 1) over (order by d) next
from
t3
)
order by
d
;
Matching neighbours are marked with * in the match column,
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With