Josh Whitlow Josh Whitlow - 5 months ago 16
MySQL Question

MYSQL - Return the most recent record before GROUP BY

I have read a lot of questions and answers regarding very similar questions as this one and the main problem is that they use

LIMIT
as a solution to returning the most recent record which won't work with this query because I need several results back.

Logic follows as:


  • Each record has an
    effective_date
    , when the records value goes into effect.

  • Only pull back records who's
    effective_date
    's are today or have passed already

  • Of the records I pull back, give me the most recent record based on the '
    date_entered
    ' for that record



So far I have been able to accomplish all but the last bit of logic with this query. I can't use an
ORDER BY
after the
GROUP BY
, because the results have already been grouped so I can't get use the
MAX()
date. I need to do the logic in the
WHERE
statement.

SELECT
aos_products.`name`,
cac_customize_agent_comp_cstm.commission_percentage_c,
cac_customize_agent_comp.date_entered
FROM
wn_writing_number
LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number.id = wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida
LEFT OUTER JOIN cac_customize_agent_comp_cstm ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp_cstm.id_c
LEFT OUTER JOIN cac_customize_agent_comp ON cac_customize_agent_comp_cstm.id_c = cac_customize_agent_comp.id
LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
LEFT OUTER JOIN aos_products ON aos_products_cac_customize_agent_comp_1_c.aos_products_cac_customize_agent_comp_1aos_products_ida = aos_products.id
WHERE
wn_writing_number.id = 'b556d816-f2e6-b78f-c776-576c0542585c'
AND wn_writing_number_cac_customize_agent_comp_1_c.deleted = '0'
AND cac_customize_agent_comp_cstm.effective_date_c <= CURDATE()
GROUP BY aos_products.`name`


WITHOUT the GROUP BY, it returns:

(varchar) (varchar) (datetime)
+---------+-------------------------+----------------------+
| name | commission_percentage_c | date_entered |
+---------+-------------------------+----------------------+
| Val 1 | 15.25 | 2016-07-06 23:57:28 |
+---------+-------------------------+----------------------+
| Val 1 | 15.75 | 2016-07-07 00:03:03 |
+---------+-------------------------+----------------------+
| Val 1 | 16 | 2016-07-07 00:31:08 |
+---------+-------------------------+----------------------+
| Val 2 | 14.75 | 2016-07-07 15:04:02 |
+---------+-------------------------+----------------------+


WITH THE GROUP BY it returns

+---------+-------------------------+----------------------+
| name | commission_percentage_c | date_entered |
+---------+-------------------------+----------------------+
| Val 2 | 14.75 | 2016-07-07 15:04:02 |
+---------+-------------------------+----------------------+
| Val 1 | 15.25 | 2016-07-06 23:57:28 |
+---------+-------------------------+----------------------+


What I am trying to get back is:

+---------+-------------------------+----------------------+
| name | commission_percentage_c | date_entered |
+---------+-------------------------+----------------------+
| Val 2 | 14.75 | 2016-07-07 15:04:02 |
+---------+-------------------------+----------------------+
| Val 1 | 16 | 2016-07-07 00:31:08 |
+---------+-------------------------+----------------------+


Because I need the most recent VAL 1 record back, something like

WHERE the datediff BETWEEN NOW() and date_entered = the smallest amount of time compared with other records

r44 r44
Answer

Try this

SELECT *
FROM
    (SELECT
        aos_products.`name` as name,
        cac_customize_agent_comp_cstm.commission_percentage_c as commission_percentage_c,
        cac_customize_agent_comp.date_entered as date_entered
    FROM
        wn_writing_number
    LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number.id = wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida
    LEFT OUTER JOIN cac_customize_agent_comp_cstm ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp_cstm.id_c
    LEFT OUTER JOIN cac_customize_agent_comp ON cac_customize_agent_comp_cstm.id_c = cac_customize_agent_comp.id
    LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
    LEFT OUTER JOIN aos_products ON aos_products_cac_customize_agent_comp_1_c.aos_products_cac_customize_agent_comp_1aos_products_ida = aos_products.id
    WHERE
        wn_writing_number.id = 'b556d816-f2e6-b78f-c776-576c0542585c'
    AND wn_writing_number_cac_customize_agent_comp_1_c.deleted = '0'
    AND cac_customize_agent_comp_cstm.effective_date_c <= CURDATE()
    ORDER BY cac_customize_agent_comp.date_entered DESC) t
GROUP BY t.name

The idea of this is to sort the records by date_entered before using group by. From this post, the groupby will use the pick the first one in the same order of normal SELECT.