query "if"'s?

[Szhark]

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?

[Jeya]

Shoot it with DECODE function


Regards

[kmesser]

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

[Victoria]

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

[kong]

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

Thanks Victoria!