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.
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. 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,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 PMSQL> 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.