Oracle:Select Maximum Value from Different Columns of the Same Row

Oracle : select maximum value from different columns of the same row

Given this test data ...

SQL> select *
  2  from your_table
  3  /

NAME         M1         M2         M3         M4
---- ---------- ---------- ---------- ----------
A             1          2          3          4
B             6          3          4          5
C             1          5          2          1

SQL>

... a straightforward GREATEST() call will give the desired result:

SQL> select name
  2          , greatest(m1, m2, m3, m4) as the greatest_m
  3  from your_table
  4  /

NAME THE_GREATEST_M
---- --------------
A                 4
B                 6
C                 5

SQL>

Note that greatest() will return NULL if any of the arguments are null. If this is a problem then use nvl() to provide a default value which won't distort the outcome. For instance, if no values can be negative....

SQL> select name
  2          , greatest(nvl(m1,0), nvl(m2,0), nvl(m3,0), nvl(m4,0)) as the greatest_m
  3  from your_table
  4  /

NAME THE_GREATEST_M
---- --------------
A                 4
B                 6
C                 5

SQL>

(Oracle)Getting max value across columns

Use greatest not max

   with x as (
      select 80 col1, 60 col2 from dual union all
      select 70, 50 from dual union all
      select 80, 90 from dual
    )
    select greatest( col1, col2 )
      from x

Max Value based on two columns

You want to use Oracle's built-in LAST (or FIRST) with MAX like this:

SELECT t.COLUMN1,
  t.COLUMN2,
  MAX(t.YEAR),
  MAX(t.MONTH) keep (dense_rank last ORDER BY YEAR nulls first) MONTH
FROM TEST t
GROUP BY COLUMN1,
  COLUMN2;

It will find the max of month in latest year only.

Get value based on max of a different column grouped by another column

You can approach this using row_number():

select key, val
from (select t.*, row_number() over (partition by key order by num desc) as seqnum
      from table_name t
     ) t
where seqnum = 1;

Whether you consider this more "elegant" is probably a matter of taste.

I should point out that this is subtly different from your query. This is guaranteed to return one row for each key; yours could return multiple rows. If you want that behavior, just use rank() or dense_rank() instead of row_number().

Fetch the rows which have the Max value for a column for each distinct value of another column

This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

"Analytic functions rock"

Edit: With regard to the first comment ...

"using analytic queries and a self-join defeats the purpose of analytic queries"

There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter, and completely standard practice.

"The default window in Oracle is from the first row in the partition to the current one"

The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.

The code works.

SQL: getting the max value of one column and the corresponding other columns

Use ROW_NUMBER() :

SELECT s.id,s.tag,s.version FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.version DESC) as rnk
   FROM YourTable t) s
WHERE s.rnk = 1

How to select records with maximum values in two columns?

Analytic functions are your friend:

SELECT   MAX( year    ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS year,
         MAX( quarter ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS quarter,
         MAX( message ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS message,
         type
FROM     info
GROUP BY type;

SQLFIDDLE

SQL - How to select a row having a column with max value in Oracle

I believe this is what you're looking for:

select *
from table
where value = (select max(value) from table);

---

Related Topics