CODEWITHSUNDEEP
sqloracle-database
Stanley Mungai
Stanley Mungai

Reputation: 4150

Select the record with the greatest value in Oracle

I am stuck with a query where one table has many records of the same id and the columns have different values like this:

ID  Name Location  Daysdue  date
001 MINE NBI       120      13-FEB-2013
001 TEST MSA       111      14-FEB-2013
002 MINE NBI       13       13-FEB-2013
002 MINE MSA       104      15-FEB-2013

I want to return the one record with the highest days due, so I have written a query:

select id,max(daysdue),name,location,date group by id,name,location,date;

This query is not returning one record but several for each id because I have grouped with every column bearing that the columns are different. What is the best way to select the row with the largest value of Days due based on id irrespective of the other values in the other columns??

For example I want to return this as:

001 MINE NBI       120      13-FEB-2013
002 MINE MSA       104      15-FEB-2013

Upvotes: 0

Views: 1478

Answers (3)

sqlab
sqlab

Reputation: 6436

SELECT 
  id,daysdue,name,location,date 
FROM your_table 
  WHERE daysdue = (
    SELECT max(daysdue) FROM your_table
  );

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

It appears that you want something like

SELECT *
  FROM (SELECT t.*,
               rank() over (partition by id
                                order by daysDue desc) rnk
          FROM table_name t)
 WHERE rnk = 1

Depending on how you want to handle ties (two rows with the same id and the same daysDue), you may want the dense_rank or row_number function rather than rank.

Upvotes: 4

Daniel Sparing
Daniel Sparing

Reputation: 2173

First you need to find the MAX(daysdue) for each ID:

SELECT
    ID,
    MAX(daysdue) AS max_daysdue
FROM table
GROUP BY ID;

Then you can join your original table to this.

SELECT t.* 
FROM
    table t
    JOIN (
        SELECT
            ID,
            MAX(daysdue) AS max_daysdue
        FROM table
        GROUP BY ID
    ) m ON
        t.ID = m.ID
        AND t.daysdue = m.max_daysdue;

Note that you might have duplicate id's in case of a tie -- makes sense semantically I guess.

Upvotes: 0

Related Questions