A long title for a simple problem: You have a table containing several columns of, say, dates. You want to find out the first or last date among the values of these columns per row. Not too bad. But, you can’t just use greatest() or least(). Well, you could before MySQL 5.0.13. After that, the presence of any NULL values in the columns compared would result in NULL being returned. This is the proper implementation of the SQL standard, but it also means everyone depending on the old way was out of luck.
And so too are we who still need to find the extreme value among columns that can contain NULL! The problem might have been solved by MySQL releasing two new functions at the same time as the greatest()/least() changes that would perform the same operation as their old behavior, ignoring NULL values. It’s a common enough use case. But complaining won’t write queries.
The first attempt I made used a combination of least() with coalesce(). This function returns the first non-NULL value among those passed. The problem with this approach is that any of the column values per row can be NULL along with any of the others. So I would have needed to cover all possible permutations, namely the number of columns to compare across squared! This gets immediately tedious. Consider the case for just four columns:
1 2 3 4 5 6 7 8 9 10 | SELECT id, date1, date2, date3, date4, least( COALESCE(date1, date2, date3, date4), COALESCE(date2, date1, date3, date4), COALESCE(date3, date2, date4, date1), COALESCE(date4, date1, date2, date3), # 12 more permutations... ) AS first_date FROM my_db.my_table GROUP BY id; |
Yuck. I nearly wrote a script that would take the columns to be compared and generate the needed query.
Luckily, fellow information wrangler Bill Karpovich suggested a much more elegant (and blissfully shorter) version:
1 2 3 4 5 6 7 8 9 | SELECT id, date1, date2, date3, date4, least( ifnull(date1, '9999-99-99'), ifnull(date2, '9999-99-99'), ifnull(date3, '9999-99-99'), ifnull(date4, '9999-99-99') ) AS first_date FROM my_db.my_table GROUP BY id; |
This checks each column to be examined, and considers NULL values to be equal to ’9999-99-99′, which I feel safe in assuming is greater than any valid date I will have. This method fulfills this common need nicely, and in syntax only requires a number of statements linearly increasing with the number of columns.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
Most implementations of SQL don’t allow the mixing of grouped and ungrouped columns in the SELECT clause. MySQL does, but the row selected for display from the ungrouped column is essentially randomly chosen. The groupwise max solution is a means of overcoming this.
http://jan.kneschke.de/projects/mysql/groupwise-max/
Thanks! I was looking for hours for this!
My solution for two columns with decimals:
SELECT LEAST(price_month_regular, ifnull(price_month_action, 9999)) AS least FROM `cp_contracts` ORDER BY least
Hello !
I’m trying to compare 5 columns for the highest and lowest values in these with some cells/rows as null value; Following above example when I am trying to run the query it doesn’t bring least value in the “Resultvalue” column, it brings NULL cell/row with no Value in these, where ever there is null in the col1 to col5 and only brings the least correct value results if no col is null/ where null doesnt get substituted with 999 value as per this query. Please guide
Use Db;
Create table Test As
SELECT *,
least(ifnull(col1,’999′),
ifnull(col2, ’999′),
ifnull(col3, ’999′),
ifnull(col4, ’999′),
ifnull(col5, ’999′)
) AS Resultvalue,
From table Db.Test
Group by Id;
The code can be improved to take care for the case when all dates are NULL:
2
3
4
5
6
7
8
9
10
11
nullif(
least(
ifnull(date1, '9999-99-99'),
ifnull(date2, '9999-99-99'),
ifnull(date3, '9999-99-99'),
ifnull(date4, '9999-99-99')
),
'9999-99-99') AS first_date
FROM my_db.my_table
GROUP BY id;