CODEWITHSUNDEEP
mysqlsql
Steve Aged
Steve Aged

Reputation: 143

MySql Select query with date

i have a question about a MySql query.

An example row is like this:

----------------------------------------------------------------------------
|    date1   | date1Name |   date2    | date2Name |   date3    | date3Name |
----------------------------------------------------------------------------
| 2018-01-02 |   Steve   | 2018-01-01 |    Alex   | 2018-02-01 |    Luke   |
----------------------------------------------------------------------------

Is it possible in one query to select the dateXName that corresponds to the most recent date? In this case Luke (of date3Name)..

I tried with the MAX and GREATEST functions but I can't do it :(

Many thanks..

Upvotes: 0

Views: 113

Answers (3)

Gaj
Gaj

Reputation: 886

Try this

create table tst (
  date1 date, date1Name varchar(20),
  date2 date, date2Name varchar(20),
  date3 date, date3Name varchar(20)
);
insert into tst values (
    str_to_date('2018-01-02', '%Y-%m-%d'), 
    'Steve', 
    str_to_date('2018-01-01', '%Y-%m-%d'), 
    'Alex', 
    str_to_date('2018-02-01', '%Y-%m-%d'),
    'Luke');

    Select case 
                when date1 is not null and date1 = max(x.dt) Then date1Name
                when date2 is not null and date2 = max(x.dt) Then date2Name
                when date3 is not null and date3 = max(x.dt) Then date3Name
            end as val
    from ( 
    Select date1 dt from tst 
    union 
    select date2 from tst 
    union 
    select date3 from tst 
    ) x, tst

Note: this will work if we know no. of columns and one row in base table

Upvotes: 0

HamzaNig
HamzaNig

Reputation: 1029

You can use union all and order by desc and limit 1 with multi query check code bellow :

select name,date from (
select date1Name as name,date1 as date from table 
union all
select date2Name as name,date2 as date from table
union all
select date3Name as name,date3 as date from table ) as dd  order by date desc limit 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use a giant case expression:

select (case when greatest(date1, date2, date3) = date1 then date1name,
             when greatest(date1, date2, date3) = date2 then date2name,
             when greatest(date1, date2, date3) = date3 then date3name
        end) as latestName
from t;

Note: This assumes that all the dates are non-NULL (as in your example). In the event of ties, this would return the first value.

Upvotes: 1

Related Questions