I want to obtain all the possible values of a VARCHAR2(1) column named
FOO, sort them (either ASC or DESC) and finally fetch to two top-most
rows. NULL values must be listed on top (ASC) or bottom (DESC). So I've
written these queries:

SELECT * FROM (
SELECT DISTINCT FOO
FROM TABLE
ORDER BY FOO NULLS FIRST
) WHERE ROWNUM<=2
-- Returns: NULL, 'A'

SELECT * FROM (
SELECT DISTINCT FOO
FROM TABLE
ORDER BY FOO DESC NULLS LAST
) WHERE ROWNUM<=2
-- Returns: 'Y', 'Z'

So far so good. Now I'd like to nest this subquery in an IN condition:

SELECT *
FROM ANOTHER_TABLE
WHERE FOO_FK IN (.......)

However, the IN condition always filters out NULL values. Something like
FOO IN (NULL, 'A', 'B') is FALSE even when FOO is null; apparently,
because FOO=NULL is not TRUE. But I need to consider rows with NULLs.

I don't know before hand if there're NULLs in my "top 2", so...

WHERE FOO_FK IS NULL OR FOO_FK IN (.......)

is not an option.


Can you think of any trick to overcome this?


The server runs Oracle 9.2.0.1.0

-----

Just before hitting "Send" I came to this idea:

SELECT *
FROM ANOTHER_TABLE
WHERE NVL(FOO_FK, 'xxxxxxxxx') IN (
SELECT NVL(FOO, 'xxxxxxxxx') FROM (
SELECT DISTINCT FOO
FROM TABLE
ORDER BY FOO NULLS FIRST
) WHERE ROWNUM<=2
)

It works but... It looks so ugly! :-?



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
with
tofu as (select foo
from (select distinct foo
from table
order by foo nulls first)
where rownum<=2)
select *
from another_table t
where exists
(select 1
from tofu tf
where tf.foo = t.foo
or tf.foo is null and t.foo is null)

--
Peter

"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
with
tofu as (select foo
from (select distinct foo
from table
order by foo nulls first)
where rownum<=2)
select *
from another_table t
where exists
(select 1
from tofu tf
where tf.foo = t.foo
or tf.foo is null and t.foo is null)

--
Peter

"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
with
tofu as (select foo
from (select distinct foo
from table
order by foo nulls first)
where rownum<=2)
select *
from another_table t
where exists
(select 1
from tofu tf
where tf.foo = t.foo
or tf.foo is null and t.foo is null)

--
Peter

"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
with
tofu as (select foo
from (select distinct foo
from table
order by foo nulls first)
where rownum<=2)
select *
from another_table t
where exists
(select 1
from tofu tf
where tf.foo = t.foo
or tf.foo is null and t.foo is null)

--
Peter

On 12 dic, 02:53, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:
Why on earth could someone define a column as varchar2(1)??

Cheers.

Carlos.

On 12 dic, 02:53, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:
Why on earth could someone define a column as varchar2(1)??

Cheers.

Carlos.

On 12 dic, 02:53, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:
Why on earth could someone define a column as varchar2(1)??

Cheers.

Carlos.

On 12 dic, 02:53, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:
Why on earth could someone define a column as varchar2(1)??

Cheers.

Carlos.

Carlos escribió:
What's your suggestion to store a single character?



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--