MySQL MAX CASE WHEN and GROUP By

Question

I'm currently having some trouble on MySQL. I'm trying to get the largest 'end' where end <=timeend group by timeend. Currently I using :

SELECT 
 timestart, 
 timeend,
 MAX(num),
 MIN(start) as start,
 (case when MAX(end)<=timeend then MAX(end) end)  AS end, value 
FROM table
GROUP BY timeend

Table is as below: (as you can see when the value is null, end is larger than timeend)

 timestart      timeend     num        start             end         value
08/15 09:46 | 08/15 10:07 | 1    | 08/15 09:48:33 | 08/15 09:48:49 | 15.3
08/15 09:46 | 08/15 10:07 | 2    | 08/15 09:48:55 | 08/15 09:49:11 | 15.3
08/15 09:46 | 08/15 10:07 | 3    | 08/16 08:00:00 | 08/16 08:00:00 | 
08/15 09:46 | 08/15 10:07 | 4    | 08/16 08:00:00 | 08/16 08:00:00 | 
08/15 09:46 | 08/15 10:07 | 5    | 08/16 08:00:00 | 08/16 08:00:00 | 
08/15 09:46 | 08/15 10:07 | 5    | 08/15 10:03:20 | 08/15 10:03:35 | 15.3
08/15 09:46 | 08/15 10:07 | 6    | 08/15 10:03:42 | 08/15 10:03:57 | 15.3
08/15 09:46 | 08/15 10:07 | 7    | 08/15 10:04:02 | 08/15 10:04:18 | 15.3
08/15 09:46 | 08/15 10:07 | 8    | 08/15 10:04:23 | 08/15 10:04:38 | 15.3
08/15 09:46 | 08/15 10:07 | 9    | 08/15 10:04:43 | 08/15 10:04:59 | 15.3
08/15 09:46 | 08/15 10:07 | 10   | 08/15 10:05:04 | 08/15 10:05:19 | 15.3
08/15 09:46 | 08/15 10:07 | 11   | 08/15 10:05:24 | 08/15 10:05:40 | 15.3
08/15 09:46 | 08/15 10:07 | 12   | 08/15 10:05:45 | 08/15 10:06:00 | 15.3
08/15 09:46 | 08/15 10:07 | 13   | 08/15 10:06:05 | 08/15 10:06:20 | 15.3

Result
08/15 09:46 | 08/15 10:07 | 13   | 08/15 09:48:33 | NULL | 15.3

The Result I expect:
08/15 09:46 | 08/15 10:07 | 13   | 08/15 09:48:33 | 08/15 10:06:20 | 15.3

timestart, timeend, start, end datatype is varchar in database ? — krunal nerikar, Sep 01 '16 at 05:55
So, the data isn't really as described above. Very confusing. :-( — Strawberry, Sep 01 '16 at 07:26

score 0 · Accepted Answer · answered Sep 01 '16 at 06:00

0

SELECT 
  timestart,
  timeend,
  MAX(num) as num,
  MIN(start) as start,
  MAX(end) as end,
  value
FROM table 
WHERE end <= timeend 
GROUP BY timestart, timeend

answered Sep 01 '16 at 06:00

Raghav Tandon

459
5
9

Wow, it works! THANKS! But still i'm not really understand why, can you explain it? THANKS A LOT! – CodingNuts Sep 01 '16 at 06:23
The WHERE condition ensures that end <= timeend, while max(end) will return you the end in that SELECTION scope. SELECTION is not same as SELECT - see: http://stackoverflow.com/questions/1031076/what-are-projection-and-selection – Raghav Tandon Sep 01 '16 at 06:30

MySQL MAX CASE WHEN and GROUP By

1 Answers1