Oracle SQL: LEAST() returns multiple rows...?

Question

A co-worker just came to me with a puzzling SQL query:

(essentially)

SELECT LEAST(id) FROM tableA A, tableB B WHERE a.name = b.name(+)

The result set returned lists three numbers however:

LEAST(id)
--------------
621
644
689

(all being IDs that meet the query as if it lacked the LEAST function all together)

Why? =)

Answer 1

LEAST(x,y,...) is not an aggregate function. It works only on its parameters. The function you want is MIN(x).

For each record, you're running LEAST(id), which will always return id. If you were passing LEAST more parameters, you would see different results. For example, LEAST(5,6,7) = 5. LEAST always returns the smallest of its parameters, whereas MIN returns the smallest of every record.