4 Replies Latest reply: Dec 4, 2012 1:51 AM by jeneesh RSS

    SQL query..

    808939
      Hi Gurus,

      I am having EMP table where data is like

      Name Salary
      ----------- -----------------
      ABC 100
      ABC 200
      ABC 300
      XYZ 400
      XYZ 500
      XYZ 600

      And i want the output like

      Name Salary
      ---------- ----------
      ABC 100
      200
      300
      XYZ 400
      500
      600

      Please help..

      Thanks,
      HP..
        • 1. Re: SQL query..
          Manik
          Try this...
          SELECT NAME,
                 CASE
                    WHEN sal > MIN (sal) OVER (PARTITION BY name ORDER BY sal) THEN
                       NULL
                    ELSE
                       MIN (sal) OVER (PARTITION BY name ORDER BY sal)
                 END          sal
            FROM emp;
          Cheers,
          Manik.
          • 2. Re: SQL query..
            908002
            with t as(select 'ABC' a, 100 b from dual
            union
            select 'ABC', 200 from dual
            union
            select 'ABC', 300 from dual
            union
            select 'XYZ', 400 from dual
            union
            select 'XYZ', 500 from dual
            union
            select 'XYZ', 600 from dual)
            select decode(row_number() over (partition by a order by a),1,a,null) a,b from t;
            • 3. Re: SQL query..
              ranit B
              try this...
              with xx as(
                  select 'ABC' a1,100 a2 from dual union all
                  select 'ABC' a1,200 a2 from dual union all
                  select 'ABC' a1,300 a2 from dual union all
                  select 'XYZ' a1,400 a2 from dual union all
                  select 'XYZ' a1,500 a2 from dual union all
                  select 'XYZ' a1,600 a2 from dual 
              )
              select decode(x1,1,a1,NULL) a1, a2 from(
              select a1,a2,row_number() over (partition by a1 order by a2) x1 from xx)
              gives
              o/p = 
              ABC     100
                   200
                   300
              XYZ     400
                   500
                   600
              Edited by: ranit B on Dec 4, 2012 1:07 PM
              -- o/p added
              • 4. Re: SQL query..
                jeneesh
                And if you are on Sql*plus, use BREAK command..
                BREAK on name