6 Replies Latest reply: Dec 3, 2012 5:46 AM by Hoek RSS

    simple query... but couldn't get it

    883189
      CREATE TABLE person_del
      (
      DNO VARCHAR2(5),
      place VARCHAR2(10),
      Got VARCHAR2(10)
      )

      commit;

      insert into person_del values('1','home','Y')

      insert into person_del values('1','office','N')

      insert into person_del values('1','other','Y')

      insert into person_del values('2','office','Y')

      insert into person_del values('3', 'home', 'N')

      insert into person_del values('3', 'office', 'Y')

      insert into person_del values('4', 'other', 'N')

      insert into person_del values('5', 'home', 'Y')

      insert into person_del values('6', 'home', 'N')

      insert into person_del values('6', 'office', 'N')

      insert into person_del values('7', 'home', 'Y')

      insert into person_del values('7', 'office', 'Y')

      commit;

      select * from person_del

      select dno,place,got,
      case when got='Y' then 'Y'
      else null
      end as "won"
      from person_del
      where place in ('home','office')


      I ran the above query, but the result is not what I wanted
      The requirement is as described below:
      List Y in a new column "won" for those dno's who achieved the place - either home or office
      If GOT=Y for place home and GOT=N for place office, then display only that column where GOT=Y
      If GOT=N for place home and GOT=Y for place office, then display only that column where GOT=Y
      If both are N then list any one of the column
      If both are Y then list any one fo the column
      The place other is not taken into account
      The output should be dno and won column

      I know this is simple...but i couldn't get it and I need it in few mins
      Can someone help me on this using CASE statement or something that isn't tricky to understand ASAP ?

      Basically, the output for this one should be as follows:

      dno won
      1 Y
      2 Y
      3 Y
      5 Y
      6 N
      7 Y
        • 1. Re: simple query... but couldn't get it
          Hoek
          How about:
          SQL> select dno
            2  ,      got won
            3  from ( select dno
            4         ,      got
            5         ,      row_number() over (partition by dno, got order by dno, got desc) cnt 
            6         from   person_del t
            7         where  t.place in ('home', 'office')
            8       )
            9  where ( case
           10            when got = 'Y' and cnt = 1 then 1
           11            when got = 'N' and cnt = 2 then 1
           12          end
           13        ) = 1;
          
          DNO   WON
          ----- ----------
          1     Y
          2     Y
          3     Y
          5     Y
          6     N
          7     Y
          
          6 rows selected.
          • 2. Re: simple query... but couldn't get it
            Solomon Yakobson
            select  dno,
                    max(got) won
              from  person_del
              where place != 'other'
              group by dno
              order by dno
            /
            
            DNO   WON
            ----- ----------
            1     Y
            2     Y
            3     Y
            5     Y
            6     N
            7     Y
            
            6 rows selected.
            
            SQL> 
            SY.
            • 3. Re: simple query... but couldn't get it
              Manik
              Another way (not as efficient as above solutions ;) , but it should work)
              SELECT dno, got
                  FROM (SELECT dno,
                               place,
                               got,
                               CASE
                                  WHEN got =
                                          LEAD (got, 1, 'X')
                                             OVER (PARTITION BY dno ORDER BY dno) THEN
                                     'Y'
                                  ELSE
                                     got
                               END
                                  got1
                          FROM person_del)
                 WHERE place <> 'other' AND got1 = 'Y'
                 group by dno, got
              ORDER BY 1;
              output:
              DNO     GOT
              ------------------------
              1     Y
              2     Y
              3     Y
              5     Y
              6     N
              7     Y
              Cheers,
              Manik.
              • 4. Re: simple query... but couldn't get it
                883189
                Hi Hoek,

                Thanks for ur reply. But, seems like the query doesn't satisfy one condition.
                I inserted a new row into the table as follow:

                insert into person_del values('10','office','N')

                It should display this row also in the output. But, it isn't displaying the row. The query is missing these values.
                • 5. Re: simple query... but couldn't get it
                  jeneesh
                  select  dno,max(got) won
                  from  person_del
                  where place in ('home', 'office')
                  group by dno
                  order by to_number(dno);
                  
                  DNO   WON      
                  ----- ----------
                  1     Y          
                  2     Y          
                  3     Y          
                  5     Y          
                  6     N          
                  7     Y          
                  10    N   
                  Edited by: jeneesh on Dec 3, 2012 5:17 PM
                  This solution was already there - by solomon - Did not notice it untill Hoek commented..
                  • 6. Re: simple query... but couldn't get it
                    Hoek
                    Guess you'll have to fiddle a bit with the possible combinations:
                    SQL> select dno
                      2  ,      got won
                      3  from ( select dno
                      4         ,      got
                      5         ,      row_number() over (partition by dno, got order by dno, got desc) cnt
                      6         ,      count(*) over (partition by dno order by dno) cnt2       
                      7         from   person_del t
                      8         where  t.place in ('home', 'office')
                      9       )
                     10  where ( case
                     11            when got = 'Y' and cnt = 1 then 1
                     12            when got = 'N' and cnt = cnt2 then 1
                     13          end
                     14        ) = 1
                     15  order by to_number(dno);
                    
                    DNO   WON
                    ----- ----------
                    1     Y
                    2     Y
                    3     Y
                    5     Y
                    6     N
                    7     Y
                    10    N
                    
                    7 rows selected.
                    I went for a CASE approach in order to show you that you can use it in your predicate (WHERE-clause) as well.
                    Solomon's solution is more concise, elegant and problably more performant as well ( and a sign for me to stop playing with queries after midnight ;) ).