Oracle Query - Getting conditional max value

Question

In Oracle, is there an easy way to perform this in a single query, or is this the correct approach?

SELECT MAX(ID) INTO n_FOO_ID FROM FOO WHERE BAR = n_InputVar and ConditionalFlag IS NULL;

IF n_FOO_ID IS NULL OR n_FOO_ID = 0 THEN
    SELECT MAX(ID) INTO n_FOO_ID FROM FOO WHERE BAR = n_InputVar;
END IF;

What I'm trying to do is get the maximum ID where my condition matches an input variable, but prioritizing rows that don't have a conditional flag.

Most of my database experience is in SQL Server, where I would do something like this:

SELECT TOP 1 ID INTO @FooID FROM FOO WHERE Bar = @InputVar ORDER BY ConditionalFlag, ID DESC

But this doesn't seem to work the same on Oracle, or the more likely...I'm not doing it correctly. Can anyone provide any advice?

Thanks!

Tony Andrews · Accepted Answer · 2010-11-17 17:37:27Z

4

I believe the Oracle equivalent of:

SELECT TOP 1 ID 
INTO @FooID 
FROM FOO 
WHERE Bar = @InputVar ORDER BY ConditionalFlag, ID DESC

is

SELECT ID FROM
( SELECT ID 
  INTO FooID 
  FROM FOO 
  WHERE Bar = InputVar ORDER BY ConditionalFlag, ID DESC
) WHERE ROWNUM = 1;

answered Nov 17, 2010 at 17:37

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brosto Over a year ago

Thanks! That did exactly what I needed it to. I tried something similar, but I had the 'where rownum = 1' on the inner query. I didn't think to try wrapping an outer query around that. Thanks again!

Collectives™ on Stack Overflow

Oracle Query - Getting conditional max value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related