Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Suppose I want to select the largest value for the timestamp < XYZ for a list of grps . E.g. given the following table table :

+---------------------+-------+-----+
| timestamp           | value | grp |
+---------------------+-------+-----+
| 2012-02-01 00:00:00 | 1     | 3   |
+---------------------+-------+-----+
| 2012-02-02 00:00:00 | 2     | 3   |
+---------------------+-------+-----+
| 2012-01-01 00:00:00 | 3     | 4   |
+---------------------+-------+-----+
The query SELECT grp, max(value) FROM table WHERE timestamp <= '2012-02-01 00:00:00' AND grp IN (3, 4) GROUP by grp; results in
+-------+------------+
| grp   | max(value) |
+-------+------------+
| 3     | 1          |
+-------+------------+
| 4     | 3          |
+-------+------------+
All good. By I want the query to return 0 for value if there's no data for the given timestamp range for a particular grp.
SELECT grp, max(value) FROM table WHERE timestamp <= '2012-01-01 00:00:00' AND grp IN (3, 4) GROUP by grp; results in only one row, containing no data for grp = 3. How do I write a query which would have zero for it instead, producing the following result?
+-------+------------+
| grp   | max(value) |
+-------+------------+
| 3     | 0          |
+-------+------------+
| 4     | 3          |
+-------+------------+
One way is to join your groups to your maxes using an inline queries like so and use COALESCE
SELECT grps.grp, 
       COALESCE(values.val, 0) val 
FROM   (SELECT grp 
        FROM   table 
        GROUP  BY grp) grps 
       LEFT JOIN (SELECT grp, 
                         Max(value) val 
                  FROM   table 
                  WHERE  timestamp <= '2012-02-01 00:00:00' 
                  GROUP  BY grp) values 
              ON grps.grp = values.grp 
If your groups are in a table you don't need inline queries at all just a good left join
SELECT
  grps.grp,
  COALESCE(max(value),0)
  groups grps
  left join `table` t
  on grps.grp =  t.grp
   and  timestamp <= '2012-02-01 00:00:00' 
GROUP BY
  grps.grp
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.