MySQL - How can I SELECT rows with MAX date

Question

My table is:

id_clocking | project_id | date       | comments        | next_steps | dependencies
1           | 39         | 2014-08-01 | new_com         | step1      | dependency1
2           | 39         | 2014-08-10 | NULL            | step2      | NULL
3           | 39         | 2014-08-12 | NULL            | step3      | dependencyx
4           | 39         | 2014-08-05 | commentx        | stepx      | depend
5           | 42         | 2014-07-15 | NULL            | NULL       | dependency_3
6           | 42         | 2014-07-03 | comment_3       | step_c     | dependency_4
7           | 42         | 2014-08-01 | NULL            | step_d     | NULL

I need to create a PHP output from the MySQL database by selecting the newest comments, next_steps, dependencies holding the maximum value of the date for each project_id

Result would be:

 project_id | comments  | next_steps | dependencies
 ------------ ------------------------------------
    39      | commentx  | step3      | dependencyx
    42      | comment_3 | step_d     | dependency_3

And should return only the rows with values based on the highest date where available (where we have ----- there is no value and that rows should not be considered) Could you please share your ideas?

And should return only the rows with values based on the highest date where available (where we have ----- there is no value and that rows should not be considered) — me_sleepin, Aug 06 '14 at 12:56
Please take 5 minutes and read this page http://stackoverflow.com/help/formatting. And improve your question readability. — mtk, Aug 06 '14 at 12:59
Why is id 42 with dependency_3 in the result? should't it be "-------" ? — Ovi Faur, Aug 06 '14 at 13:05
Why not this row? -> `6 | 42 | 2014-07-03 | comment_3 | step_c | dependency_4` — Strawberry, Aug 06 '14 at 13:18
Ah, you mean you're only ignoring NULL dependencies? So, NULL next_steps are OK? — Strawberry, Aug 06 '14 at 13:19
You may want to take a look at this link below: [http://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate][1] [1]: http://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate — user3766876, Aug 06 '14 at 13:27

Strawberry · Answer 1 · 2014-08-06T14:06:53.847

I suspect that this is MASSIVELY over-convoluted - but I seem to be having a senior moment, so...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (id_clocking INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,project_id INT NOT NULL
 ,date        DATE NOT NULL
 ,comments        VARCHAR(20) NULL
 ,next_steps VARCHAR(20) NULL
 ,dependencies VARCHAR(20) NULL
 );

 INSERT INTO my_table VALUES
 (1,39,'2014-08-01','new_com','step1','dependency1'),
 (2,39,'2014-08-10',NULL,'step2',NULL),
 (3,39,'2014-08-12',NULL,'step3','dependencyx'),
 (4,39,'2014-08-05','commentx','stepx','depend'),
 (5,42,'2014-07-15',NULL,NULL,'dependency_3'),
 (6,42,'2014-07-03','comment_3','step_c','dependency_4'),
 (7,42,'2014-08-01',NULL,'step_d',NULL);

 SELECT i.project_id
      , j.comments
      , k.next_steps
      , l.dependencies
   FROM 
      ( SELECT project_id
             , MAX(CASE WHEN type = 'comment' THEN max_date END) comment_date
             , MAX(CASE WHEN type = 'next_steps' THEN max_date END) next_steps_date
             , MAX(CASE WHEN type = 'dependencies' THEN max_date END) dependencies_date
          FROM 
             ( SELECT a.project_id
                    , a.type
                    , MAX(a.date) max_date
                 FROM 
                    ( SELECT project_id
                           , date
                           , 'comment' type
                           , comments FROM my_table 
                       WHERE comments IS NOT NULL
                       UNION
                      SELECT project_id
                           , date
                           , 'next_steps' 
                           , next_steps 
                        FROM my_table 
                       WHERE next_steps IS NOT NULL
                       UNION
                      SELECT project_id
                           , date
                           , 'dependencies' 
                           , dependencies 
                        FROM my_table 
                       WHERE dependencies IS NOT NULL
                    ) a
                GROUP
                   BY project_id
                    , type
             ) m
         GROUP
            BY project_id
      ) i
   JOIN my_table j
     ON j.project_id = i.project_id 
    AND j.date = i.comment_date
   JOIN my_table k
     ON k.project_id = i.project_id 
    AND k.date = i.next_steps_date
   JOIN my_table l
     ON l.project_id = i.project_id 
    AND l.date = i.dependencies_date;

 +------------+-----------+------------+--------------+
 | project_id | comments  | next_steps | dependencies |
 +------------+-----------+------------+--------------+
 |         39 | commentx  | step3      | dependencyx  |
 |         42 | comment_3 | step_d     | dependency_3 |
 +------------+-----------+------------+--------------+

This seems like a simpler solution... (and faster too: http://sqlfiddle.com/#!2/b9552e/5)

SELECT x.project_id
     , c.comments
     , n.next_steps
     , d.dependencies
  FROM 
     ( SELECT project_id
            , MAX(CASE WHEN comments IS NOT NULL THEN date END) comments_date
            , MAX(CASE WHEN next_steps IS NOT NULL THEN date END) next_steps_date
            , MAX(CASE WHEN dependencies IS NOT NULL THEN date END) dependencies_date 
         FROM my_table 
        GROUP 
           BY project_id
     ) x
  JOIN my_table c
    ON c.project_id = x.project_id
   AND c.date = x.comments_date
  JOIN my_table n
    ON n.project_id = x.project_id
   AND n.date = x.next_steps_date
  JOIN my_table d
    ON d.project_id = x.project_id
   AND d.date = x.dependencies_date
 WHERE c.comments IS NOT NULL
   AND n.next_steps IS NOT NULL
   AND d.dependencies IS NOT NULL;

   +------------+-----------+------------+--------------+
   | project_id | comments  | next_steps | dependencies |
   +------------+-----------+------------+--------------+
   |         39 | commentx  | step3      | dependencyx  |
   |         42 | comment_3 | step_d     | dependency_3 |
   +------------+-----------+------------+--------------+

Looks even more complicated than my solution, but both are working [fiddle](http://sqlfiddle.com/#!2/b9552e/3). I renamed `my_table` to `example8`. +1 for your work — VMai, Aug 06 '14 at 13:58
The execution plan of your new one looks best of all, see [newest Fiddle](http://sqlfiddle.com/#!2/b9552e/7). I've added one solution with joins but the result didn't impress me. — VMai, Aug 06 '14 at 14:17

VMai · Answer 2 · 2014-08-06T13:56:45.707

As I understand your question you want to every project_id

the newest comments item that is not null
the newest next_steps item that is not null
the newest dependencies item that is not null

Those items can be from different rows. If that's the case you could do it with this query:

SELECT
    project_id,
    (SELECT e2.comments FROM example8 e2 WHERE e2.date = (
        SELECT MAX(date) FROM example8 e1 
        WHERE e1.comments IS NOT NULL and e2.project_id = e1.project_id
        GROUP BY e1.project_id )
     AND e.project_id = e2.project_id) as comments,
    (SELECT e2.next_steps FROM example8 e2 WHERE e2.date = (
        SELECT MAX(date) FROM example8 e1 
        WHERE e1.next_steps IS NOT NULL and e2.project_id = e1.project_id
        GROUP BY e1.project_id )
     AND e.project_id = e2.project_id) as next_steps,
    (SELECT e2.dependencies FROM example8 e2 WHERE e2.date = (
        SELECT MAX(date) FROM example8 e1 
        WHERE e1.dependencies IS NOT NULL and e2.project_id = e1.project_id
        GROUP BY e1.project_id )
     AND e.project_id = e2.project_id) as dependencies
FROM     example8 e
GROUP BY e.project_id;

Demo: Solutions of Strawberry and mine

@me_sleepin I have to say that Strawberrys second solution is the best. So it would only be fair that you accept his answer. Please do so. — VMai, Aug 06 '14 at 18:12

score -1 · Answer 3 · answered Aug 06 '14 at 13:06

-1

To get results run this query:
SELECT * FROM tableName AS a WHERE date = ( SELECT MAX(date) FROM tableName AS b )

answered Aug 06 '14 at 13:06

Inbali

107
1
7

score -1 · Answer 4 · answered Aug 06 '14 at 13:07

-1

The query shown below should work:

Select 
    tableA.project_id, comments, next_steps, dependencies
from
    tableA,
    (select 
        max(date) date, project_id
    from
        tableA
    group by project_id) tableB
WHERE
    tableA.date = tableB.date
        AND tableA.project_id = tableB.project_id;

Please change column and table names as you need.

answered Aug 06 '14 at 13:07

sri

338
2
13

Again, a good solution for a different problem... although some of us try to discourage 'implicit' (comma-) join syntax. – Strawberry Aug 06 '14 at 14:23

MySQL - How can I SELECT rows with MAX date

4 Answers4