Use most recent creation date in group by statement

Question

I have an invoicing system and am trying to generate reports on hours spent. I'm saving every instance of a change to the order, so there are multiple entries for almost every item on every invoice. Due to this, I'm filtering out the old changes and am trying to only use the most recent.

Each instance sharing a project_id, phase_id, and the same weekstart are the same item on the invoice. I want to generate a report and only grab the most recent versions of those items.

Example table:

    id       project_id      phase_id        weekstart     created
    ---------------------------------------------------------------
    1        6               apple         2017-04-20    2017-04-23
    2        6               apple         2017-04-20    2017-04-24
    3        8               banana        2017-04-20    2017-04-23
    4        9               pear          2017-04-20    2017-04-23
    5        9               pear          2017-04-20    2017-04-25

I want to be able to run a query to get:

    id       project_id      phase_id        weekstart     created
    ---------------------------------------------------------------
    2        6               apple         2017-04-20    2017-04-24
    3        8               banana        2017-04-20    2017-04-23
    5        9               pear          2017-04-20    2017-04-25

Currently I'm using something like:

SELECT * from invoiceitems where employee_id = 10 
group by project_id, phase_id, weekstart

But this doesn't account for the creation date.

Ordering the results doesn't have any affect on the group by statement. I've checked for similar posts, but only two I found are looking to order by the highest creation date altogether or aren't grouping the results by multiple columns.

`SELECT *` is not a valid `SELECT` clause in a `GROUP BY` query. A `GROUP BY` query doesn't return rows from the table, it **generates** on the fly the row it puts on the recordset; for each generated row it uses a group of rows extracted from the query. There are multiple values for `id` in a group (because `id` is not in the `GROUP BY` clause), that's why `id` in the `SELECT` clause is ambiguous. A `GROUP BY` query **is not** the solution to your question. — axiac, Jul 19 '17 at 06:30
Take a look at [this answer](https://stackoverflow.com/a/28090544/4265352) on a similar question. — axiac, Jul 19 '17 at 06:40

Tim Biegeleisen · Answer 1 · 2017-07-19T23:10:53.473

1

Join to a subquery which finds the latest creation times for each project. Note that we use GROUP BY here, but only in the subquery, to aggregate over projects.

SELECT t1.*
FROM invoiceitems t1
INNER JOIN
(
    SELECT project_id, phase_id, MAX(created) AS max_created
    FROM invoiceitems
    GROUP BY project_id
) t2
    ON t1.project_id = t2.project_id AND
       t1.phase_id   = t2.phase_id
       t1.created    = t2.max_created

edited Jul 19 '17 at 23:10

answered Jul 19 '17 at 06:29

Tim Biegeleisen

502,043
27
286
360

I *think* `phase` also needs to be considered – Strawberry Jul 19 '17 at 07:33
@Strawberry You might be right, but given that the sample data provided does not seem to reveal this I won't change my query unless the OP leaves a comment confirming your hunch. – Tim Biegeleisen Jul 19 '17 at 09:35
phase_id does need to be included. As well as weekstart. Can I just group on all three? – John Baker Jul 19 '17 at 18:09
I added `phase_id` to the logic. You can also add `weekstart` if your logic requires that. – Tim Biegeleisen Jul 19 '17 at 23:12

Muhammad Tarique · Answer 2 · 2017-07-19T08:11:52.373

-2

Tested and works perfectly

SELECT MAX(`id`) as `id`, `project_id`, `phase_id`, 
`weekstart`, MAX(`created`) as `created` 
FROM `invoiceitems` 
GROUP BY `project_id` 
ORDER BY `project_id` ASC

edited Jul 19 '17 at 08:11

answered Jul 19 '17 at 06:50

Muhammad Tarique

1,407
1
13
17

There is nothing to guarantee that `MAX(id)` and `MAX(created)` return the values that are on the same row on the original table. Also, when you `GROUP BY project_id` and you `SELECT phase_id`, if there are two rows with the same `project_id` but different `phase_id` the server [is free to choose whatever value it wants for `phase_id`](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html). The result of your query is indeterminate. – axiac Jul 19 '17 at 08:13
As per the question I think that the `id` is an auto-increment column and most probably the latest `created` date will have the higher `id` – Muhammad Tarique Jul 19 '17 at 08:19
And also what I am seeing in question it looks like `project_id` and `phase_id` have some relation between them that's why both 6 is for apple and 9 for pear. – Muhammad Tarique Jul 19 '17 at 08:31
And I think you shouldn't be voting negative of someones effort. You should have looked at what the question is and what was asked... – Muhammad Tarique Jul 19 '17 at 08:34
You did a lot of assumptions and ignored the information in the question. It says *"Each instance sharing a project_id, phase_id, and the same weekstart are the same item on the invoice"* -- The part with "same project_id, phase_id and weekstart" is completely missing from your answer. From this point of view, the query posted in the question, even incorrect, generates correct results often than yours. – axiac Jul 19 '17 at 08:43
I think downvoting misinformed and misleading answers is fair. – Strawberry Jul 19 '17 at 09:56
Take a look at [this answer](https://stackoverflow.com/a/28090544/4265352) on a similar question. – axiac Jul 19 '17 at 10:02

Use most recent creation date in group by statement

2 Answers2