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`
?

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
``````