query "if"'s?

Suppose I have a table:

name type
a 1
a 2
b 1
c 2
d null


How can I select out the name, based on the fact If there is a type of 1 and 2, take the 2, if there's only a 1, take the 1, and if there's a null, make it a 1?

I was thinking this:

SELECT ....
WHERE MAX(nvl(table.type, 1)) in (1,2)

Is that the most efficient way?

Shoot it with DECODE function


Regards

select name, nvl(max(type),1) type from mytable group by name;

There is a better function in Oracle 8i called CASE. It is like decode but more flexible and fast.

select case
if [condition] then ...
if [condition] then ...
...
end

Sweet! I didn't know about the CASE function!

Thanks Victoria!