mikepop mikepop - 19 days ago 6
MySQL Question

mySQL case when only returning 1 result

No idea why this is returning the first result only. The date is a Date format not datetime. I've only started using MySQL from using ORACLE and this is doing my head in.

SELECT *,

CASE d_date

WHEN max(d_date) THEN 'Today'

WHEN date_add(max(d_date), interval -1 day) THEN 'Yesterday'

ELSE 'other'

END dateID

FROM mike.Tble

Answer

When aggregate function like MAX used it returns single record with max value. Thus my understandings need a sub query. I could not find how to optimize this query for a large record but I have following and lets optimize this for large records.

set @today = (SELECT max(d_date) FROM mike.Tble);
set @yesterday = (SELECT date_add(max(d_date), interval -1 day) FROM mike.Tble);

SELECT *, CASE d_date

     WHEN @today THEN 'Today'

     WHEN @yesterday THEN 'Yesterday'

     ELSE 'other'

  END AS dateID

FROM mike.Tble

Note: I edited query and its performance is better then last query

Comments