CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Shiva
Shiva

Reputation: 51

query with LEAST returning multiple rows in oracle

When I ran the below query in Oracle 11g

SELECT least(val)
FROM
(
    SELECT 1 AS val
    FROM dual
    UNION
    SELECT 2 AS val
    FROM dual
    UNION
    SELECT 3 AS val
    FROM dual
);

I was expecting a single row but it is returning multiple rows. please help me out where exactly my understanding is going wrong..

Upvotes: 0

Views: 507

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

Oracle's LEAST function returns the least value in a list of expressions, e.g. LEAST(1, 2, 3) would return 1. So LEAST could be used to find the minimum value across a collection of columns, e.g. LEAST(col1, col2, col3). What you are seeing is to be expected, i.e. you are getting back three records with the smallest value of each record.

Instead, if you want the minimum over an aggregate of rows, then you should be using MIN, e.g.

select min(val)
from
(
    select 1 as val from dual union all
    select 2 from dual        union all
    select 3 from dual
);

Upvotes: 2

Related Questions