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 Go to next message
OraFerro
Messages: 386
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 #679688 is a reply to message #679687] Tue, 17 March 2020 03:42 Go to previous messageGo to next message
OraFerro
Messages: 386
Registered: July 2011
Senior Member
I just had a successful trial, but not sure if this is the best way especially that my actual data is 1000s of records:

SELECT *
  FROM test_data
  WHERE id IN (SELECT id
        FROM (SELECT DISTINCT id,
                              value
            FROM TEST_DATA)
        GROUP BY id
        HAVING COUNT(*) > 1);
Thanks
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67132
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.
Re: different rows in set of data (using lead or alternative) [message #679690 is a reply to message #679689] Tue, 17 March 2020 04:20 Go to previous message
OraFerro
Messages: 386
Registered: July 2011
Senior Member
Hi Michel,

I changed my working example to the last example of yours as it repeats (select from test_data) only once, which is actually a very lengthy query.

Thanks a lot
Ferro
Previous Topic: Import CSV data from CLOB
Next Topic: ORACLE :::Is there any other way to drop the column with out uncompression table in oracle database
Goto Forum:
  


Current Time: Mon May 25 13:52:59 CDT 2020