aCarella aCarella - 13 days ago 6
SQL Question

Getting all row data from minimum value in a field in a SQL query (Oracle)

I'm having trouble obtaining a row of all information from a SQL query by specifying the minimum value of a field. Below is a basic example of the data I'm using and what I am trying to obtain:

SELECT 1 AS NUM_, 'ABC' AS LET_ FROM DUAL
UNION
SELECT 2 AS NUM_, 'DEF' AS LET_ FROM DUAL
UNION
SELECT 3 AS NUM_, 'GHI' AS LET_ FROM DUAL;


The query above will yield the following result:

NUM_ | LET_
-----------
1 | ABC
2 | DEF
3 | GHI


I only want the row that has the data
1
and
ABC
. This is what I tried, below:

SELECT MIN(LN.NUM_) AS MIN_NUM,
LN.LET_
FROM (SELECT 1 AS NUM_, 'ABC' AS LET_ FROM DUAL
UNION
SELECT 2 AS NUM_, 'DEF' AS LET_ FROM DUAL
UNION
SELECT 3 AS NUM_, 'GHI' AS LET_ FROM DUAL) LN
GROUP BY LET_;


But the above still gives me all of the rows. How do I zero in on just the row with the lowest value in the
NUM_
column, giving me
1
and
ABC
?

Answer

Your query finds the minimum NUM_ for each LET_. TO get the minimum NUM_ for among all the records use ORDER BY & ROWNUM

Try this

SELECT LN.NUM_ AS MIN_NUM, 
       LN.LET_ 
FROM   (SELECT 1 AS NUM_, 'ABC' AS LET_ FROM DUAL
        UNION
        SELECT 2 AS NUM_, 'DEF' AS LET_ FROM DUAL
        UNION
        SELECT 3 AS NUM_, 'GHI' AS LET_ FROM DUAL
        ORDER BY LN.NUM_) LN
Where ROWNUM = 1
Comments