开发者

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜