How to select MAX(date) after a certain date

Question

Background:

I'm writing a Python Script to get some data. I have 2 tables engine_hours and machines in an SQL database. I want to get the latest received data that has received in the previous 2 days (which is done with thisdate_recorded >=).

The tables:

The first table:

engine_hours
=============
machine_id       
date_recorded
value

The second table:

machines
========
id
title

Sample Data table:

The first table:

                    engine_hours
==================================================
 machine_id  |      date_recorded       | value
-------------+--------------------------+---------       
     1       |   16/10/2018  20:30:02   |   10
     3       |   16/10/2018  19:02:32   |   42
     2       |   16/10/2018  20:32:56   |   13
     2       |   16/10/2018  19:23:23   |   12
     1       |   16/10/2018  16:54:59   |   10
     1       |   16/10/2018  16:52:59   |   10
     1       |   14/10/2018  10:24:59   |   10

The second table:

    machines
==================
  id  |   title
------+-----------   
   1  |  ABC-123
   2  |  DEF-456  
   3  |  GHI-789

Desired Output:

=============================================================
 machine_id  |  title  |       date_recorded      | value    
     1       | ABC-123 |   16/10/2018  20:30:02   |   10
     2       | DEF-456 |   16/10/2018  20:32:56   |   13
     3       | GHI-789 |   16/10/2018  19:02:32   |   42

What I have tried:

I have tried 4 different queries, but failed miserably:

engine_hours_query = "SELECT ma.`title`, eh.`machine_id`, eh.`value`, eh.`date_recorded` " \
                     "FROM `engine_hours` AS eh inner join `machines` AS ma " \
                     "WHERE eh.`machine_id` IN ({}) AND eh.`date_recorded` >=  \"{}\" " \
                     " AND eh.`machine_id` = ma.`id`".format(", ".join([str(m_id) for m_id in list_of_machine_ids]),
                                                             cut_off_date)

engine_hours_query_2 = "SELECT `machine_id`, `value`, `date_recorded` FROM `engine_hours` AS eh " \
                       "WHERE `date_recorded` = ( SELECT MAX(`date_recorded`) " \
                       "FROM `engine_hours` AS eh2  " \
                       "WHERE eh.`machine_id` = eh2.`machine_id`)"


engine_hours_query_3 = "SELECT `machine_id`, `value`, `date_recorded` FROM `engine_hours` AS eh " \
                       "WHERE `date_recorded` = ( SELECT MAX(`date_recorded`) " \
                       "FROM `engine_hours` AS eh2 ) " \
                       "WHERE eh.`date_recorded` >=  \"{}\"".format(cut_off_date)

engine_hours_query_4 = "SELECT ma.`title`, eh.`machine_id`, eh.`value`, eh.`date_recorded` " \
                     "FROM `engine_hours` AS eh inner join `machines` AS ma " \
                     "WHERE eh.`machine_id` IN ({}) AND eh.`date_recorded` >=  \"{}\" " \
                     " AND eh.`machine_id` = ma.`id`".format(", ".join([str(m_id) for m_id in list_of_machine_ids]),
                                                             cut_off_date)

Research:

Can you include the table DDL, not just the column names, this should be easy unless your date_recorded field is not declared as a date type of some kind. Please also include which RDBMs this is for. — Andrew, Oct 15 '18 at 09:06
@3kstc - you mean mysql? or something else like Oracle, SQL Server, Postgres etc? — Andrew, Oct 15 '18 at 09:32
How is the max after a certain date different from the overall max? — Gordon Linoff, Oct 15 '18 at 12:10

score 1 · Accepted Answer · answered Oct 16 '18 at 01:43

1

This version should do what you want:

SELECT eh.machine_id, eh.value, eh.date_recorded
FROM engine_hours eh
WHERE eh.date_recorded = (SELECT MAX(eh2.date_recorded)
                          FROM engine_hours eh2
                          WHERE eh.machine_id = eh2.machine_id
                         );

You might want to add AND eh.date_recorded >= NOW() - INTERVAL 2 DAY, if that condition is still necessary.

answered Oct 16 '18 at 01:43

Gordon Linoff

1,242,037
58
646
786

I'm guessing I'll need an `inner join` to look up the `eh.machine_id`s corresponding `machine_title` from `machines` table – 3kstc Oct 16 '18 at 03:12
@3kstc . . . Yes. You seem to know how to do that. – Gordon Linoff Oct 16 '18 at 11:46

score 0 · Answer 2 · answered Oct 15 '18 at 09:08

You want to select the MAX date from a subset of data. The criteria for the subset is that it has a date_recorded that is greater than 2 days ago

SELECT MAX(eh.`date_recorded`)
FROM `engine_hours` eh
WHERE eh.`date_recorded` >= DATE_SUB(NOW(), INTERVAL 2 DAY);

Depending on how complicated your requirements get, you might need to move to using subqueries, but for now, the WHERE clause happens before the MAX operation, so this query restricts all your data to just the last 2 days, and then gives the MAX date within it

How to select MAX(date) after a certain date

2 Answers2