MySql Select query with date

Question

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..

use group by ... having max(date).

Dev web
– Dev web

2018-07-10 10:42:19 +00:00
Commented Jul 10, 2018 at 10:42 — Dev web
– Dev web, Commented Jul 10, 2018 at 10:42
This kind of problem is symptomatic of poor design

Strawberry
– Strawberry

2018-07-10 11:47:03 +00:00
Commented Jul 10, 2018 at 11:47 — Strawberry
– Strawberry, Commented Jul 10, 2018 at 11:47

Gordon Linoff · Accepted Answer · 2018-07-10 10:42:30Z

1

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.

answered Jul 10, 2018 at 10:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Aged Over a year ago

Many thanks! what would happen if one of the three dates was empty? and if all 3 were empty?

Gordon Linoff Over a year ago

@SteveAged . . . The result would be NULL with this version because greatest() returns NULL if any argument is NULL.

HamzaNig · Accepted Answer · 2018-07-10 11:21:04Z

0

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

answered Jul 10, 2018 at 11:21

HamzaNig

1,0271 gold badge10 silver badges33 bronze badges

Comments

Gaj · Accepted Answer · 2018-07-10 11:28:10Z

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

Collectives™ on Stack Overflow

MySql Select query with date

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related