Boopathi Boopathi - 3 months ago 11
SQL Question

how to query records which value is not reducing

Given a sample table

Field 1 | Field 2 | Field 3
------------------------------
class1 | 02-09-2016 | 10.00
Class1 | 03-09-2016 | 09.20
Class1 | 04-09-2016 | 08.00
class2 | 02-09-2016 | 10.00
Class2 | 03-09-2016 | 10.00
Class2 | 04-09-2016 | 10.00
class3 | 02-09-2016 | 33.32
Class3 | 03-09-2016 | 40.12
Class3 | 04-09-2016 | 10.20


I want to query the Field1 for which Field3 is not changing for a period of time. In this case, I need to select only class2 for which Field3 is not changing between 02-09-2016 & 04-09-2016.

Can someone help?

Answer

Table setup:

create table so1 (field1 varchar2(100), field2 varchar2(100), field3 varchar2(100))

insert into so1
  select *
    from (
        select 'class1', '02-09-2016', '10.00' from dual union all
        select 'Class1', '03-09-2016', '09.20'  from dual union all
        select 'Class1', '04-09-2016', '08.00'  from dual union all
        select 'class2', '02-09-2016', '10.00'  from dual union all
        select 'Class2', '03-09-2016', '10.00'  from dual union all
        select 'Class2', '04-09-2016', '10.00'  from dual union all
        select 'class3', '02-09-2016', '33.32'  from dual union all
        select 'Class3', '03-09-2016', '40.12'  from dual union all
        select 'Class3', '04-09-2016', '10.20'  from dual 
     )

Query:

select field1, field2, field3
  from (
         select s.*, 
                count(distinct field3) over (partition by upper(field1)) cnt
           from so1 s
       ) 
 where cnt = 1

The query selects all rows/columns meeting the criteria.