CODEWITHSUNDEEP
sqloracle-database
Anastasis
Anastasis

Reputation: 194

Make rownum value null in the same values

I have the sql code and i want for every unique value from COLUMN1 the same rownum. For example in the example i want null values for rownum 2,3 and for column 2 rownum 2

   select rownum,
   t1.column1 as column1,
   t1.Column2 as column2,
   t3.Column3 as column3
from Table1 t1,Table3 t3,Table2 t2
where t3.S_ID=t2.AS_ID
and t2.KT_ID=t1.T_ID

enter image description here

How can i implement this?

Upvotes: 0

Views: 413

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

First, learn proper explicit join syntax. Second, you can readily do what you want using lag() and other analytic functions:

select (case when column1 = lag(column1) over (order by column1, column3)
             then NULL
             else dense_rank() over (order by column1)
        end) as rn,
       t1.column1 as column1,
       t1.Column2 as column2,
       t3.Column3 as column3
from Table1 t1 join
     Table2 t2
     on t2.KT_ID = t1.T_ID join
     Table3 t3
     on t3.S_ID = t2.AS_ID
order by column1, column3;

Some notes:

  • SQL queries return unordered result sets. If you want rows in a particular order -- or even in the same order the next time you run the query -- then include an order by.
  • I arbitrarily added column3 to the ordering. It can be any column that makes the sort stable. That is, each row has a unique set of order keys, so they are in the same order each time.
  • The same ordering conditions are used for the lag() functions, but only column1 is used for the dense_rank().
  • Learn explicit join syntax. It is more powerful, and almost everyone thinks it is easier to read.

Upvotes: 2

anwaar_hell
anwaar_hell

Reputation: 776

        This might help you...

       select rownum,column1,column2,column3 from (

Select rownum,column1,column2,column3,
row_number() over( partition by column1,column2 order by column1,column2) as analytic_rnm
from(
select rownum,
   column1 as column1,
   Column2 as column2,
   Column3 as column3
from  Table1 t1,Table3 t3,Table2 t2
where t3.S_ID=t2.AS_ID
and t2.KT_ID=t1.T_ID 
)X
)M
where analytic_rnm=1

union all
select NULL as rownum,
column1,column2,column3  from (
Select rownum,column1,column2,column3,
row_number() over( partition by column1,column2 order by column1,column2) as analytic_rnm
from(
select rownum,
   column1 as column1,
   Column2 as column2,
   Column3 as column3
from  Table1 t1,Table3 t3,Table2 t2
where t3.S_ID=t2.AS_ID
and t2.KT_ID=t1.T_ID
)X)Y
where analytic_rnm>1

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

select 
   case when row_number() -- if it's the first row per group...
             over(partition by column1
                  order by column3) = 1
        then dense_rank() -- ... return 1,2,3
             over(order by column1)
        else null
   end,
   t1.column1 as column1,
   t1.Column2 as column2,
   t3.Column3 as column3
from Table1 t1,Table3 t3,Table2 t2
where t3.S_ID=t2.AS_ID
and t2.KT_ID=t1.T_ID

Upvotes: 0

Related Questions