CODEWITHSUNDEEP
sqloracle-databaseplsql
Naveee
Naveee

Reputation: 13

How to remove duplicate values of a group in oracle sql

In Oracle SQL, i would like to remove/hide duplicate values.

Actual:

Column 1 Column 2
1 a
1 b
2 c
2 d
2 e
3 f

Expected:

Column 1 Column 2
1 a
b
2 c
d
e
3 f

Please help on the approaches.

Upvotes: 0

Views: 534

Answers (2)

Mahamoutou
Mahamoutou

Reputation: 1555

You could also use lag analytic function like below. Make sure the third parameter for lag function is obviously different from the first parameter for the first line in each partition group. I chose "-1 * t.Column_1", but you could simply use a literal like 999999999.

With test_t (Column_1,  Column_2) as (
select 1,   'a' from dual union all
select 1,   'b' from dual union all
select 2,   'c' from dual union all
select 2,   'd' from dual union all
select 2,   'e' from dual union all
select 3,   'f' from dual
)
select 
  case 
    when lag(t.Column_1, 1, -1 * t.Column_1) over(partition by t.Column_1 order by t.Column_2) != t.Column_1 
        then Column_1 
    else null
  end Column_1
, Column_2
from test_t t
order by t.Column_1, t.Column_2
;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143133

In SQL*Plus, that's simple:

Table contents:

SQL> select * from test order by col1, col2;

      COL1 COL2
---------- ----
         1 a
         1 b
         2 c
         2 d
         2 e
         3 f

6 rows selected.

BREAK on desired column:

SQL> break on col1
SQL>
SQL> select * from test order by col1, col2;

      COL1 COL2
---------- ----
         1 a
           b
         2 c
           d
           e
         3 f

6 rows selected.

SQL>

Any decent reporting tool (Apex, Oracle Reports Builder, ...) have an option to break the report. Depending on what you really use, "solution" might differ.

Upvotes: 2

Related Questions