This discussion is archived
6 Replies Latest reply: Dec 3, 2012 3:46 AM by Hoek RSS

simple query... but couldn't get it

883189 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 ;) ).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points