MySQL – LEAST and GREATEST Comparison Operators

The biggest challenge which we often face as a database person is to compare the value across multiple columns. Trust me, if there were no comparison operators, we would have never got this task done. MySQL supports many comparison operators like BETWEEN, IN, etc. LEAST and GREATEST is very handy if you want to compare values across many columns.

These operators accept multiple parameters and find the least and greatest value respectively

Let us create this dataset

CREATE table testing(num1 int, num2 int, num3 int);
INSERT INTO testing(num1,num2,num3)
SELECT 456,90,774 UNION ALL
SELECT 70,111,4 UNION ALL
SELECT 190,87,391;

Now that we have created the dataset. We will execute the code displayed below.

SELECT LEAST(num1,num2,num3) as least_value,
GREATEST(num1,num2,num3) as greatest_value
FROM testing;

Once you execute the code above, it will display following result set. You can clearly see that the resulset now contains two different values – least value and greatest value.

If these operators are not available, you will end up with creating lots of CASE expressions which become complex if number of columns are more than 3 or you may need to use user defined function. But these operators are very powerful and easy to use.

Let me know what you think about this blog post in the comment section below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

MySQL, SQL Function, SQL Operator, SQL Scripts
Previous Post
SQL SERVER – Unable to Open Maintenance Plan in Management Studio
Next Post
Team Database Development and Version Control with SQL Source Control

Related Posts

7 Comments. Leave new

  • Hi Dave,
    What version of MS SQL supports this functions?
    2012 don’t have it.

    Reply
  • surendra gadasala
    April 19, 2016 12:02 pm

    Hi i am using microsoft sql server management studio 2014 .I tried the above example in my management studio but i am getting the error ‘greatest’ and ‘Least’ is not a recognized built-in function name.

    Reply
  • Niels Filter
    April 19, 2016 2:07 pm

    @Jinantha and @surendra, as Pinal mentioned, these are MySql keywords, they don’t exist in MS SQL.

    Reply
    • surendra gadasala
      April 20, 2016 1:34 pm

      yes niels i realized after commenting on the blog…i read the full article later and realized that it is not for sql server…any ways thanks

      Reply
  • rezidentzero
    April 20, 2016 1:07 am

    after what version of mysql are available?

    Reply
  • Nice functions!

    Reply
  • Bhadresh Thummar
    May 11, 2016 2:49 pm

    Is there any function like LEAST or GREATEST in sql server?

    Reply

Leave a Reply

Menu
Exit mobile version