SQL Query returns the maximum date value in a query

Question

I am trying to run a query to do the following:
return all the most recent records in a table between a given date range.
I am currently using this query

SELECT id FROM schedule WHERE eventdate BETWEEN '2014-09-01' AND '2014-09-07'

Which returns records 1,2,3,4,5 from the schedule table shown below

schedule table.

+ ---- + ------------ + ------------- + ----------------- + ------------ + ---------- +
| id | eventdate | resource_id | text | added_on | added_by |
+ ---- + ------------ + ------------- + ----------------- + ------------ + ---------- +
| 1 | 2014-09-05 | 1 | Some old text | 2014-08-01 | Sam |
| 2 | 2014-09-05 | 1 | Some newer text | 2014-09-01 | Jordan |
| 3 | 2014-09-06 | 1 | another day | 2014-09-03 | Jordan |
| 4 | 2014-09-05 | 1 | Most recent | 2014-09-10 | Jordan |
| 5 | 2014-09-07 | 2 | Other resource | 2014-09-09 | Sam |
+ ---- + ------------ + ------------- + ----------------- + ------------ + ---------- +

I am trying to return only unique records in the specified date range, where the unique records to be returned are those with the highest date stamp in the column added_on.

In the above example, I would like only 3,4,5 records to be returned. Entries 1 and 2 were replaced by entry 4.

** Please note: the column added_onhas a timestamp of the date of the date (yyyy-mm-dd HH: mm: ss) and is left for clarity **

, , , , - eventdate, resource_id and added_on
, , _

Answer 1

SELECT s1.* FROM schedule s1  
inner join (select max(id) as id1 from schedule WHERE eventdate BETWEEN '2014-09-01' AND '2014-09-07' group by eventdate,resource_id ) as s2 on s2.id1=s1.id 

Answer 2

:

, eventdate, resource_id added_on... ... add_on.. by eventdate, , group by eventdate, resource_id _id.

, .

SELECT * 
FROM schedule
WHERE ID IN
(   SELECT MAX(id) 
    FROM schedule 
    WHERE eventdate BETWEEN '2014-09-01' AND '2014-09-07' 
    GROUP BY eventdate, resource_id
)

DEMO1

_,

SELECT * 
FROM schedule
WHERE added_on IN
(   SELECT MAX(added_on) 
    FROM schedule 
    WHERE eventdate BETWEEN '2014-09-01' AND '2014-09-07' 
    GROUP BY eventdate, resource_id
)

DEMO2

IN JOIN, JOIN MAX().

SELECT * 
FROM schedule s
JOIN 
(   SELECT MAX(added_on) as added_on 
    FROM schedule
    WHERE eventdate BETWEEN '2014-09-01' AND '2014-09-07' 
    GROUP BY eventdate, resource_id
) t ON t.added_on = s.added_on

DEMO3

Answer 3

You need to get the last entry for each unique combination of eventdate, resource_id, added_on, for example:

SELECT schedule.* 
FROM schedule JOIN (
   SELECT MAX(id) AS max_id 
   FROM schedule
   GROUP BY eventdate, resource_id, added_on
) t
ON t.max_id = schedule.id
WHERE eventdate BETWEEN '2014-09-01' AND '2014-09-07'

Answer 4

I think this will be the easiest request you may have. Just comment here if you have further questions.

 SELECT MAX(id) , eventdate, Max(added_on) FROM schedule GROUP BY eventdate.

It will return

+----+------------+------------+
| id | eventdate  |  added_on  | 
+----+------------+------------+
|  3 | 2014-09-06 | 2014-09-03 | 
|  4 | 2014-09-05 | 2014-09-10 | 
|  5 | 2014-09-07 | 2014-09-09 | 
+----+------------+------------+