There is a table called Test having three fields:
id sal1 sal2 1 200 300 2 400 100 3 600 200 4 500 800
I want to select the maximum sal between the two columns. The expected output is 300, 400, 600 and 800. Please help me to solve this query.
You can do this easily with a CASE expression:
select id , case when sal1 > sal2 then sal1 else sal2 end as max_sal from Test
However, if there are more than two columns, this quickly devolves into a coding nightmare; see Highest value in each row (17 May 2004) for an example.
The ideal situation would be if your database system had a function to select the highest value from a list of parameters. MySQL, PostgreSQL and Oracle (and perhaps others) support the GREATEST and LEAST functions, so you could use GREATEST like this:
select id , greatest(sal1,sal2) as max_sal from Test
Check your database system documentation under Functions.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.