1

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

2 Answers 2

2

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
0
1

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;
1
  • 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! Jul 22, 2014 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.