CODEWITHSUNDEEP
sqloracle-databaseuser-defined-functions
zaczap
zaczap

Reputation: 1396

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

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? =)

Upvotes: 4

Views: 4599

Answers (1)

Welbog
Welbog

Reputation: 60418

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.

Upvotes: 14

Related Questions