Q
Problem solve Get help with specific problems with your technologies, process and projects.

GREATEST and LEAST

There is a table called Test with three fields. 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.

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.

This was last published in August 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close