Hello, there is some creepy request:

SELECT order.id as id, CASE WHEN ((MAX(order_item_log.date) IS NOT NULL) AND (MAX(mail_history.date) IS NULL)) THEN MAX(order_item_log.date) WHEN ((MAX(mail_history.date) IS NOT NULL) AND (MAX(order_item_log.date) IS NULL)) THEN MAX(mail_history.date) WHEN MAX(order_item_log.date) >= MAX(mail_history.date) THEN MAX(order_item_log.date) WHEN MAX(order_item_log.date) < MAX(mail_history.date) THEN MAX(mail_history.date) END as date_change ....

is it possible to reuse the calculated MAX () somehow, thanks in advance

  • 3
    it’s not easier to select all three fields and decide what is needed in the code already? - Yura Ivanov
  • and you look at the explain request, something tells me that it will execute each max once. It can still be converted into IFNULL (IFNULL (IFNULL (),),) - zb '
  • Yes, it’s already so kartic, if without all these transformations with Max (), if you just choose three fields, something around 4 c. and if with all Max () increases to 16 s. - arminsus_1775
  • is this something different from SELECT GREATEST(MAX(order_item_log.date),MAX(mail_history.date)) as date_change ? sqlfiddle.com/#!2/4579d/2 - zb '
  • one
    If you are worried about performance, MySQL will not calculate the value each time. It will be calculated only once. - ReinRaus


1 answer 1

make a stored procedure that returns the desired value.