How Eliminate Duplicate records in table 9 Different ways

Table creation 

create table EMP_T1

(

  EMPNO   NUMBER,

  EMPNAME VARCHAR2(40),

  SAL     NUMBER

)

insert into emp_t1 values (1, pankaj ,1000);

insert into emp_t1 values (1, pankaj ,1000);

insert into emp_t1 values (1, pankaj ,1000);

insert into emp_t1 values (1, pankaj ,1000);

   --1 --Distinct

    ------------------

      select distinct empno,empname,sal from emp_t1

=========================================================

     --2-- Unique

      -------------

      select unique empno,empname,sal from emp_t1

==============================================================

      --3--group by 

      -------------------------------------

      select empno,empname,sal from emp_t1 group by empno,empname,sal 

     =========================================================

      ---4 union

      -----------------------------------

      select empno,empname,sal 

      from emp_t1

      union 

      select  empno,empname,sal

      from emp_t1

      ============================================================

      ---5 Intersect 

      --------------------------------

       select empno,empname,sal 

      from emp_t1

      intersect  

      select  empno,empname,sal

      from emp_t1

===========================================================

      ---6 minus

      ---------------------------------

        select empno,empname,sal 

      from emp_t1

      minus  

      select null,null,null

      from emp_t1

=========================================================

      ---7 --row_number ()

      -----------------------------------------

      select * from (

      select empno,empname,sal, row_number() over (partition by empno,empname,sal order by empno,empname,sal ) R

      from emp_t1 ) Emp

      where R=1

===============================================================

      ----8 row id 

      -----------------------------------

      select empno,empname,sal 

      from emp_t1 where rowid in

      ( select min(rowid) from emp_t1 group by empno,empname,sal )

===============================================================

      ----9 corelated subquery

      ----------------------------------------

      select empno , empname , sal 

      from emp_t1 A 

      where 1 = ( select count(1) from emp_t1 b where 

      b.empno=a.empno

      and b.empname=a.empname

      and a.sal=b.sal

      and a.rowid >=b.rowid)

================================================================      

SQL: Group By, Rollup, cube, grouping

 

   Grouping is used to identify count of distinct values in specific object. we have been applying grouping on  columns of table.

    Syntax 

•     Group by  <col_name1>, <col name2>
•     Rollup (<col_name1>, <col name2>)
•     Cube ((<col_name1>, <col name2>)
•     Grouping (<col name1>)

     We use group by to identify count or sum against distinct values of columns in table

      select col1 , col2, count(1) from table_t group by col1, col2.

here it is identified distinct value between col1 & col2 and display no of rows

Rollup   --- this function used to grouping in next level

     n --- No of columns

if we are doing rollup between 2 columns then you have need to do rollup next 3 level (n+1) level.

 

 First level --- It shows normal goruping

 2nd level   --- Grouping at distinct value of first columns

 3rd level    --  Grand total 

Cube   ---- This function used to grouping in next level

   n --- column numbers

( N*2)

so we have calculate level is 4

first level --- Shows normal grouping

2nd level -- Grouping at distinct value of first column

3rd level  -- Grouping at distinct value  of 2nd column

4th level  -- Grouping ( Grand Total)

==============================================

Testing script

create table group_test_t

( col1 number,

  col2 number)

 --- here my table has created

 -- I am going to understand grouping , gorup by ,rollup and cube function here

    --col1 has 2 distinct values and col2 has 3 distinct value

 ---step 2-- Insert data into table

   insert into group_test_t (col1,col2) values (1,1);

   insert into group_test_t (col1,col2) values (2,2);

   insert into group_test_t (col1,col2) values (2,3);

   ---step3 --- test group by function using count function now I am going find for 2 col 

   select col1,col2 ,count(1) as nu_rows from group_test_t group by col1,col2 order by 1

   --rollup always as n+1 level suppose there 2 col then leve would 3 level

   select col1,col2 ,sum(col1) as nu_rows from group_test_t group by rollup(col1,col2)  

      level 1 -- normal grouping 

      level 2-- grouping for each distict value in col1

      level 3 --- total grouping 

      col1  has value 1,  2

      col2 have value 1,2 ,3

      grouping normal 1,1 ==> 

                      2,2

                      2,3 

       level 2 grouping at distinct col1 

       1      ===> 1

       2 (2 times) ==> 2+2 =4

       level 3 --->

       1,2,3

              ==> level 2 grouping as 4 + 1 =5

              

   Now understand cube where level calculate as n*2

     select col1,col2 ,sum(col1) as nu_rows from group_test_t group by cube(col1,col2)              

       level 1-- Noraml Goruping

       level 2 --> grouping for distict value for col1

       level 3 --> grouping for distict value for col2

       level 4 --> total sub group.