I have tested this, and I am pretty sure it works correctly according to our
requirement.
WITH is1234
AS (SELECT tab1.col2 tab1col2,
tab1.col3 tab1col3,
tab2.col1 tab2col1,
tab2.col2 tab2col2,
tab2.col3 tab2col3
FROM tab1 JOIN tab2 ON tab2.col2 = tab1.col2
WHERE tab2.col1 = 1234),
isnot1234
AS (SELECT tab1.col2 tab1col2,
tab1.col3 tab1col3,
tab2.col1 tab2col1,
tab2.col2 tab2col2,
tab2.col3 tab2col3
FROM tab1 JOIN tab2 ON tab2.col2 = tab1.col2
WHERE tab2.col1 <> 1234)
SELECT CASE
WHEN (SELECT COUNT (*) FROM isnot1234) > 0
THEN
(SELECT tab1col3
FROM isnot1234 o
WHERE NOT EXISTS
(SELECT 1
FROM isnot1234 i
WHERE i.tab2col3 > o.tab2col3))
ELSE
(SELECT tab1col3
FROM is1234 o
WHERE NOT EXISTS
(SELECT 1
FROM is1234 i
WHERE i.tab2col3 < o.tab2col3))
END
ans
FROM DUAL;
On Fri, Jun 17, 2011 at 9:31 AM, rich p <[email protected]> wrote:
> There are two analytic functions you might want to look into: first_value
> or last_value. It depends on how you look at the query problem at hand. You
> can get by using just one because if you think about it, the "FIRST_VALUE"
> of an ordered series sorted in DESCENDING will also be the "LAST_VALUE" of
> that same series when sorted in ASCENDING order.
>
> You can verify this using your test table and the following query sample:
>
> select tab1.col3, tab2.col3,
> first_value ( tab1.col3 ) over ( order by tab2.col3 desc ) as test_1,
> first_value ( tab1.col3 ) over ( order by tab2.col3 asc ) as test_2
> from tab1, tab2
> where tab2.col2 = tab1.col2
>
> One thing you will have to consider however is that the set you order by in
> descending order is further constrained in your "where" clause by the value
> in tab2.col1 ( = 1234 or <> 1234 ) so the set you will be manipulating using
> with each analytic function call will be a different and mutually exclusive
> subset of the above query.
>
> Rich Pascual
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
