Select max value from different column in a row in oracle database

Question

My table in oracle database is like this:

Rollno | mark1 | Mark2 | mark3 --------+-----------+-----------+--------+ 101 | 10 | 20 | 30 102 | 22 | 44 | 08 103 | 55 | 11 | 14 --------+-----------+-----------+--------+

And I want output like

Rollno | MAX(Mark1,mark2,mark3) --------+-------------------------+ 101 | 30 102 | 44 103 | 55 --------+-------------------------+

Any query suggestion?

Which dbms? Some products have a GREATEST function. NULL possible? — jarlh
– jarlh, Commented Jun 12, 2015 at 9:10

Matt · Accepted Answer · 2015-06-12 09:18:34Z

4

Use GREATEST function

SELECT Rollno, GREATEST (mark1, Mark2, mark3) AS Maxv FROM yourtable

answered Jun 12, 2015 at 9:18

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jarlh Over a year ago

Sorry, saw Oracle first after asking.

jarlh · Accepted Answer · 2015-06-12 09:27:43Z

ANSI SQL compliant answer, written before Oracle was specified:

Use CASE to find largest column value:

select Rollno, case when mark1 > mark2 and mark1 > mark3 then mark1 when mark2 > mark3 then mark2 else mark3 end as max_value from tablename

(NULL's not considered here.)

Collectives™ on Stack Overflow

Select max value from different column in a row in oracle database

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related