CODEWITHSUNDEEP
oracle-database
Nag
Nag

Reputation: 15

about oracle SQL query need to skip the repeated value in one column

I have a data in my table like this:

CustID  CUSTName
10      Tony
10      Jony
10      Hony
20      Bot
20      Guly
20      Easter

I need output like below:

custID Custname
10     Tony
       Jony
       Hony
20     Bot
       Guly
       Easter

Upvotes: 0

Views: 1020

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

If you're using SQL*Plus or SQL Developer you can use the built-in formatting options; in this case with break:

The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:

BREAK ON break_column

So in your case:

break on custid

select custid, custname
from your_table
order by custid;

This isn't something you'd generally want to do as part of the query itself; this is about how the retrieved data is displayed by the client/report/application rather than how it is queried.

Upvotes: 1

mikron
mikron

Reputation: 683

Try the following.

with src as
 (select 10 CustID, 'Tony' CUSTName
    from dual
  union all
  select 10, 'Jony'
    from dual
  union all
  select 10, 'Hony'
    from dual
  union all
  select 20, 'Bot'
    from dual
  union all
  select 20, 'Guly'
    from dual
  union all
  select 20, 'Easter'
    from dual)

select case
          when Rnum = 1 then
           Custid
       end CustId, CustName
  from (select row_number() over(partition by custid order by custid) Rnum,
                src. custId, Src. CustName
           from Src)

Here I've used the row_number() analytic function

Upvotes: 0

Related Questions