GROUP BY Precedence in MySQL

Question

I have two tables; one is called rules and the other data. The Rules table holds events, which have a description, id and date_created and is simply used to categorize events.

The data table has a date and id column; This stores the actual dates of an event, as events can span up to months long in dates.

My issue is this: I wish to select everything from data and group it by date, so each date is represented only once. However, the event with the most recent creation date should have precedence if there is a collision, i.e. two events happen on the same day. Here is what I've tried, which doesn't offer control over date_created:

SELECT d.date, r.description FROM data d LEFT JOIN rules r ON d.id = r.id GROUP BY date ORDER BY d.date

I haven't included date_created yet because I'm stuck, and not sure where it should go in the query to get the desired effect. Any ideas would be greatly appreciated!

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Nick, Sep 17 '18 at 03:35

Fahmi · Answer 1 · 2018-09-17T04:46:18.107

1

From your question it seems to me that at first you need to select maximum date of event creation and then using subquery you can desired result:

SELECT a.date, b.description
FROM data a
INNER JOIN (
    SELECT id, description,MAX(date_created) as mdate
    FROM rules
    GROUP BY id,description
) b ON a.id = b.id AND a.date = b.mdate

edited Sep 17 '18 at 04:46

answered Sep 17 '18 at 04:33

Fahmi

37,315
5
22
31

GROUP BY Precedence in MySQL

1 Answers1