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.
WHEN max(d_date) THEN 'Today'
WHEN date_add(max(d_date), interval -1 day) THEN 'Yesterday'
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