How can I SELECT the first row with MAX(Column value)?
Solution 1
Why does your second query not work...
select Item_No,
Quantity
from Rec_details
group by Item_No,
Quantity
having Quantity=max(Quantity);
You are grouping by both Item_No
and Quantity
and the Item_No
appears to be the primary key and contain unique values so each group will only contain one row. The HAVING
clause looks within the group so it will check that the value of quantity
is the maximum value within that group but there is only one value within the group so this will always be true. Your query is the equivalent of:
SELECT DISTINCT
Item_No,
Quantity
FROM Rec_details;
Some other ways to get the maximum value:
Oracle 11g R2 Schema Setup:
create table Rec_details (item_no, Quantity ) AS
SELECT 12507,1 FROM DUAL UNION ALL
SELECT 12549,4 FROM DUAL UNION ALL
SELECT 12100,8 FROM DUAL UNION ALL
SELECT 12501,2 FROM DUAL UNION ALL
SELECT 12201,7 FROM DUAL UNION ALL
SELECT 12509,3 FROM DUAL UNION ALL
SELECT 12080,1 FROM DUAL;
Query 1 - Get one row with maximum quantity
and latest item_no
(using 1 table scan):
SELECT MAX( item_no ) KEEP ( DENSE_RANK LAST ORDER BY Quantity ) AS Item_no,
MAX( Quantity ) AS Quantity
FROM Rec_Details
| ITEM_NO | QUANTITY |
|---------|----------|
| 12100 | 8 |
Query 2 - Get one row with maximum quantity
and latest item_no
(using 1 table scan):
SELECT *
FROM (
SELECT *
FROM Rec_details
ORDER BY Quantity DESC, Item_no DESC
)
WHERE ROWNUM = 1
| ITEM_NO | QUANTITY |
|---------|----------|
| 12100 | 8 |
Query 3 - Get all rows with maximum quantity
(using 1 table scan):
SELECT Item_no, Quantity
FROM (
SELECT r.*,
RANK() OVER ( ORDER BY Quantity DESC ) AS rnk
FROM Rec_details r
)
WHERE rnk = 1
| ITEM_NO | QUANTITY |
|---------|----------|
| 12100 | 8 |
Query 4 - Get all rows with maximum quantity
(using 2 table scans):
SELECT Item_no,
Quantity
FROM Rec_Details
WHERE Quantity = ( SELECT MAX( Quantity ) FROM Rec_Details )
| ITEM_NO | QUANTITY |
|---------|----------|
| 12100 | 8 |
Solution 2
One way to get the maximum in Oracle is to use order by
and rownum
:
select rd.*
from (select Item_No, Quantity
from Rec_details
order by quantity desc
) rd
where rownum = 1;
The group by
seems unnecessary.
If there are multiple rows with the same quantity, you can also do:
select rd.*
from rec_details rd
where rd.quantity = (select max(quantity) from rec_details);
Solution 3
Oracle 12 finally introduced a fetch clause, so this can be done pretty elegantly:
SELECT Item_No, Quantity
FROM Rec_details
ORDER BY 2 DESC
FETCH FIRST ROW ONLY
Related videos on Youtube
Mureinik
A software engineering manager who likes nothing more than when his employees prove him wrong.
Updated on September 15, 2022Comments
-
Mureinik about 1 year
I want to select only one row from a table. This row holds the maximum number in the table. I have tried to use MAX Fun but it didn't work for me. I use two tables to run my query, the first query returns more than one row
SELECT Rec_No FROM Records WHERE STATUS = 'Record Replaced'; select Item_No, Quantity from Rec_details group by Item_No, Quantity having Quantity=max(Quantity);
I have the problem in the second query as I always get these records
Item_No Quantity ---------- ---------- 12507 1 12549 4 12100 8 12501 2 12201 7 12509 3 12080 1
My answer should check if the records replaced from the Records table and then select the maximum quantity and Item_no from Rec_Details. In my case it should be:
Item_No Quantity ---------- ---------- 12201 7
-
Admin almost 8 yearsSQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"
-
Admin almost 8 yearsYour method has worked ! Thank you but how do I alter in my other query?
-
Admin almost 8 yearsSQL Error: ORA-00923: FROM keyword not found where expected
-
MT0 almost 8 years
LIMIT
is not valid syntax in Oracle - in Oracle 12 you can useFETCH FIRST ROW ONLY
as per Mureinik's answer.