Home » SQL & PL/SQL » SQL & PL/SQL » different rows in set of data (using lead or alternative) (11.2.0.1.0)
different rows in set of data (using lead or alternative) [message #679687] |
Tue, 17 March 2020 03:27 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
Hope all of you and your families are safe.
I have the following example where each ID has values for 3 months (1,2,3). I need to get data for IDs where value for month 1, 2, 3 are not identical (if there is one month that is different in value that the other 2 then I need to keep the three rows, if all are identical, I need to remove them all).
I tried using lead in where clause but I get an error that window functions are not allowed.
Example:
create table test_data
(
id number(2),
month number(2),
value number(4)
);
insert all
into test_data values(1,1,55)
into test_data values(1,2,55)
into test_data values(1,3,55)
into test_data values(2,1,55)
into test_data values(2,2,55)
into test_data values(2,3,35)
into test_data values(3,1,55)
into test_data values(3,2,95)
into test_data values(3,3,55)
into test_data values(4,1,51)
into test_data values(4,2,52)
into test_data values(4,3,53)
select * from dual;
select * from test_data where value <> lead(value,1) over (PARTITION by id order by month);
-- I get an error as described
-- I even tried:
select id, month, value, lead(value,1) over (PARTITION by id order by month) back1, lead(value,2) over (PARTITION by id order by month) back2
from TEST_DATA;
to get all values in one row and then add case but i looked very complicated and no way to filter all rows without the lag as well!
Thanks,
Ferro
[Updated on: Tue, 17 March 2020 03:29] Report message to a moderator
|
|
|
|
Re: different rows in set of data (using lead or alternative) [message #679689 is a reply to message #679688] |
Tue, 17 March 2020 04:12 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Here are 2 queries:
SQL> select *
2 from test_data
3 where id in (select id from test_data group by id having count(distinct value) > 1)
4 order by 1, 2
5 /
ID MONTH VALUE
---------- ---------- ----------
2 1 55
2 2 55
2 3 35
3 1 55
3 2 95
3 3 55
4 1 51
4 2 52
4 3 53
9 rows selected.
SQL> select id, month, value
2 from (select id, month, value,
3 count(distinct value) over (partition by id) cnt
4 from test_data)
5 where cnt > 1
6 order by 1, 2
7 /
ID MONTH VALUE
---------- ---------- ----------
2 1 55
2 2 55
2 3 35
3 1 55
3 2 95
3 3 55
4 1 51
4 2 52
4 3 53
9 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 16:01:00 CDT 2024
|