Aggregate function / query in Oracle 11g

Question

I am working in Oracle 11g.

I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert)

I have written an inline view (with tons of LEFT OUTER JOINS and CASE statements) that sets up the following scenario:

ID (non-unique) | Received 
----------------------------
 1 Y
 1 N
 2 N
 2 N
 2 Y
 3 N
 3 Y

I need to query the inline view:

If each ID has at least 1 row with a 'Y', return 'Y'.

Otherwise return 'N'.

So for the scenario above, I would return a 'Y'.**

I thought an aggregate function would work, and I've been reading through GROUP BY, ANY/ALL, and Count. However, I did not see

Not asking anyone to write my SQL for me ... if anyone could even suggest a function or technique or article I could read / run down. I guess I don't know enough about complex SQL to know what to search for.

If this is not possible to write, I guess I could look into doing it as a PL/SQL Function. I know that I can use a cursor and track things with variables. However, I thought it would be more efficient to write it as pure SQL (seem to recall the Oracle Optimizer can't see into functions/procedures).

Thank you very much in advance,

Philip

Answer 1

Simple, but "dirty":

with t as (
 select 1 as id, 'Y' as received from dual union
 select 1 as id, 'N' as received from dual union
 select 2 as id, 'N' as received from dual union
 select 2 as id, 'N' as received from dual union
 select 2 as id, 'Y' as received from dual union
 select 3 as id, 'N' as received from dual union
 select 3 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;
M
-
Y

Change input (e.g. change id to 4 in last line):

with t as (
 select 1 as id, 'Y' as received from dual union
 select 1 as id, 'N' as received from dual union
 select 2 as id, 'N' as received from dual union
 select 2 as id, 'N' as received from dual union
 select 2 as id, 'Y' as received from dual union
 select 3 as id, 'N' as received from dual union
 select 4 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;
M
-
N

Answer 2

If I understand your logic correctly, a two-level aggregation should do what you need. You don't need PL/SQL or client-side processing (i.e.: .NET).

SELECT MIN(Received) Received FROM (
 SELECT ID, MAX(Received) Received FROM your_view GROUP BY ID
);

http://sqlfiddle.com/#!4/b2f85/3/0

Or @Balazs style is even cleaner, two-level aggregation without the subquery:

SELECT MIN(MAX(Received)) Received FROM your_view GROUP BY ID;

• Philip Tenn: Wanted to accept both answers, upvoted yours as well, but as you mentioned, @balazs was slightly cleaner. Thank you for looking at this, and thank you for the SQL Fiddle, that was awesome!