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

12,574

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:

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 |

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
Share:
12,574

Related videos on Youtube

Mureinik
Author by

Mureinik

A software engineering manager who likes nothing more than when his employees prove him wrong.

Updated on September 15, 2022

Comments

  • Mureinik
    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
    Admin almost 8 years
    SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"
  • Admin
    Admin almost 8 years
    Your method has worked ! Thank you but how do I alter in my other query?
  • Admin
    Admin almost 8 years
    SQL Error: ORA-00923: FROM keyword not found where expected
  • MT0
    MT0 almost 8 years
    LIMIT is not valid syntax in Oracle - in Oracle 12 you can use FETCH FIRST ROW ONLY as per Mureinik's answer.