aCarella - 7 months ago 31

SQL Question

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`

`ABC`

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

`1`

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

Source (Stackoverflow)