Oracle Query - Getting conditional max value
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 F开发者_如何学运维OO 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!
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;
精彩评论