'How can I SELECT the first row with MAX(Column value)?

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
sqloracleselect


Solution 1:[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:

SQL Fiddle

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

Results:

| 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

Results:

| 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

Results:

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

Results:

| ITEM_NO | QUANTITY |
|---------|----------|
|   12100 |        8 |

Query 5 - Get one row with maximum Quantity and latest Item_No using Oracle 12 Syntax (1 table scan):

SELECT *
FROM   Rec_Details
ORDER BY Quantity DESC, Item_No DESC
FETCH FIRST ROW ONLY;

Query 5 - Get all rows with maximum Quantity using Oracle 12 Syntax (1 table scan):

SELECT *
FROM   Rec_Details
ORDER BY Quantity DESC
FETCH FIRST ROW WITH TIES;

Solution 2:[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:[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

Solution 4:[4]

use

select item_no, quantity from rec_details where quantity = (select max(quantity) from rec_details);

This one is a little bit costly but works fine if you have multiple row with the highest value and you want to get all the rows with highest value.

Solution 5:[5]

Try

select Item_No, Quantity from Rec_details
ORDER BY Quantity DESC LIMIT 1;

Solution 6:[6]

MySQL Syntax

SELECT Item_No, Quantity FROM Rec_details ORDER BY Item_no DESC, Quantity DESC LIMIT 1

SQL Server / MS Access Syntax

SELECT TOP 1 Item_No, Quantity FROM Rec_details ORDER BY Item_no DESC, Quantity DESC

Oracle Syntax

SELECT Item_No, Quantity FROM Rec_details WHERE ROWNUM <=1 ORDER BY Item_no DESC, Quantity DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Gordon Linoff
Solution 3
Solution 4
Solution 5 cantelope
Solution 6

Related Questions