How to get 1st non-zero row from a group?-CodePudding

my table looks like this. The name of the table as table1

column1    column2   column3
  a           0       date1
  a           0       date2
  a           4       date3
  a           7       date4
  b           0       date1 
  b           6       date2
  b           0       date3
  b           4       date4
  c           2       date1
  c           1       date2
  c           5       date3
  c           9       date4
  d           0       date1
  d           0       date2
  d           8       date3
  d           0       date4

i want my output as

column1        column2
   a              4
   b              6
   c              2
   d              8

From above table you may have already notice that I'm interested in grouping column1 and getting the 1st non zero number from column2. Any help will be appreciated a lot. Thanks

CodePudding user response：

You can use a not exists query:

select *
from t
where column2 <> 0 and not exists (
    select *
    from t as x
    where x.column1 = t.column1
    and   x.column2 <> 0
    and   x.column3 < t.column3
)

CodePudding user response：

Hello other solution in oracle

     select * from (
select  a.* 
, case when column2 <> 0 then row_number()over(partition by column1 , case when column2 <>0 then 1 else 0 end order by (select 1 from dual)) end as rn 
from yourtable a) where rn=1;