Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to write query for this purpose

Re: how to write query for this purpose

From: cc <chris.colclough_at_nospam.jhuapl.edu>
Date: Fri, 22 Dec 2006 09:33:51 -0500
Message-ID: <emgqci$4n7$2@aplcore.jhuapl.edu>


Another option, using analytics:
select *
from
(
select wono, jobno, status, count(*) over (partition by wono, status) as count_wono_by_status,
count(*) over (partition by wono) as count_all_wono, row_number() over (partition by wono order by wono) as wono_rownum

from worktran
)
where status = 'Fullfill'
and count_wono_by_status = count_all_wono and wono_rownum = 1
"MadhavC" <choudharymv_at_gmail.com> wrote in message news:1166770724.491303.127960_at_79g2000cws.googlegroups.com...
> We have a worktran table
>
> WoNo Jobno Status
> 1 01 Fullfill
> 1 02 Pending
> 2 01 Fullfill
> 2 02 Fullfill
>
>
> We need to display those WoNo having all its rows status=fulfill
>
> In the example above, it should display only WoNo 2 because it contain
> all Fullfill status
> We tried - select distinct WoNo from worktran where status='Fullfill'
> But it does not make sure that all of the rows for certian WoNo have
> status='Fulfill'
>
> Thanks in advance.
>
Received on Fri Dec 22 2006 - 08:33:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US