CODEWITHSUNDEEP
mysql
Devidas
Devidas

Reputation: 181

How to get latest date in 3 colums row wise in mysql?

How to get latest date in 3 colums row wise in mysql?

|id | cont_no| date1     | date2      | date3    
|1  |    1   |01-02-2011 | 06-06-2015 | 22-03-2017    
|2  |    1   |21-02-2011 | 10-04-2012    
|3  |    2   |08-01-2011 |    
|4  |    1   |25-01-2011 |

I want to get max date row wise, For example first row get 22-03-2017,second row get 10-04-2012.

i have query like this

SELECT date,date2,date3 FROM devi
WHERE date IN (SELECT max(date) FROM devi where date2 IN(SELECT max(date2) FROM devi where date3 IN (SELECT max(date3) FROM devi)))

Upvotes: 1

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

You could try chaining a couple of calls to GREATEST for each row:

SELECT
    id,
    cont_no,
    GREATEST(
        GREATEST(COALESCE(date1, '1000-01-01'), COALESCE(date2, '1000-01-01')),
        COALESCE(date3, '1000-01-01')) AS max_date
FROM yourTable;

enter image description here

Demo

This kind of problem can be symptomatic of poor design. Consider whether having multiple date columns is the best strategy. And store dates using a proper date data type. Assuming that you are currently storing your dates as text, and not as date types.

Upvotes: 1

Related Questions