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

by

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>

More Related Contents:

  1. Update statement with inner join on Oracle
  2. Splitting string into multiple rows in Oracle
  3. GROUP BY with MAX(DATE) [duplicate]
  4. Is there any function in oracle similar to group_concat in mysql? [duplicate]
  5. Can a foreign key be NULL and/or duplicate?
  6. Is there any boolean type in Oracle databases?
  7. Stored Procedure with optional “WHERE” parameters
  8. subquery in FROM must have an alias
  9. SQL multiple columns in IN clause
  10. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  11. Dynamic Oracle Pivot_In_Clause
  12. How can I select from list of values in Oracle
  13. Selecting the second row of a table using rownum
  14. How to debug ORA-01775: looping chain of synonyms?
  15. Does Oracle store trailing zeroes for Number data type?
  16. Oracle Update Query using Join
  17. Generating dates between two dates
  18. How to convert Rows to Columns in Oracle? [duplicate]
  19. SQL: getting the max value of one column and the corresponding other columns [duplicate]
  20. SQL not a single-group group function
  21. Oracle: Combine multiple results in a subquery into a single comma-separated value [duplicate]
  22. SQL Keep getting error with ON UPDATE CASCADE
  23. Finding rows that don’t contain numeric data in Oracle
  24. adding primary key to sql view
  25. How do I call an Oracle stored procedure from an Excel VBA script?
  26. Return number from Oracle Select statement after parsing date
  27. How do you interpret a query’s explain plan?
  28. How to call Oracle MD5 hash function?
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment