Resort Picnic After Lockdown | Funny Moments And Highlights

Mysql | Order By Comparing Multiple Columns & Ignore Specific Value While Sorting | Greatest & Least

> SELECT *,greatest(maturity,death) as insurance_date FROM `insurance` order by insurance_date DESC

> SELECT *,least(maturity,if(death='0000-00-00 00:00:00',maturity,death)) as insurance_date FROM `insurance` order by insurance_date DESC


In MYSQL, GREATEST and LEAST are the functions which compares columns columns rather than rows. Best example is an Insurance company’s data where admins want to figure out the day a customer gets his/her money by comparing which date comes first, a maturity date or death date.

Subscribe my Channel:-

http://www.youtube.com/user/SanketRooney?sub_confirmation=1

Facebook Page:-

https://www.facebook.com/UnpossibleNS

Twitter Account:-

https://twitter.com/UnpossiblePOG

Blog :-

https://unpossiblepog.blogspot.in/

------------------------------------------------

Hey guys, in this video I am going to sort the table by comparing multiple columns.

For example, I have an insurance table, and two columns, maturity and death have the date-time as data-type.

First we shall sort it by the maximum date among maturity and death, so we shall use the GREATEST function and add columns which will be compared.

We give that value of function as “insurance_date” and then use the  ORDER BY clause.

Now as you can see, it gives the output based on which date is maximum and sort accordingly.

However in insurance, the lowest date matters because company has to give money based on which date occurs first.

So for that we are going to use LEAST function.

But the problem is, if a person is not died then, of course the date field will be empty so the output is incorrect.

To fix that, we have to overwrite the the death date. We are going to use the IF condition inside LEAST function, and if death date is zero, we overwrite it with a maturity date. Also, Maturity Date shouldn’t be empty in any case.

And now we got the output we wanted.

Query link is given in the description.

Don’t forget to like, share and subscribe.Thanks for watching.