CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
jordan elphick
jordan elphick

Reputation: 41

SQL Query return highest date value in query

Im trying to run a query to do the following:
return all of the most recent entries in the table between a given date range.
Currently I am 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 thats 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'm trying to only return the unique records within the specified date range, where the unique records that should be returned are those with the highest date timestamp in the added_on column.

In the example above I would like for only records 3,4,5 to be returned. Records 1 and 2 have been superseded by record 4.

**Please note: The added_on column is of type date timestamp (yyyy-mm-dd HH:mm:ss) and has been left off for clarity **

I do not know the number of rows the query will return, the information that determines the uniqueness of the record is the eventdate, resource_id and added_on
i.e. there should only be one record returned per resource per day, and this record should have the highest added_on value

Upvotes: 4

Views: 248

Answers (4)

slek
slek

Reputation: 309

I think this will be the simplest query you can have. Just comment here if you have further question.

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

This 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 | 
+----+------------+------------+

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25842

NOTE:

you said you determine the uniqueness of each day by the three columns eventdate, resource_id, and added_on... but you only want the greatest added_on per day... so do not group by added_on.. group by eventdate to get data by day, group by eventdate, resource_id to get data by day per resource_id.

try getting the unique id's and then filter an outer query by them

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

or if you want to use the highest added_on you can do it the same way

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

if you want to get rid of using IN and use a JOIN you can do it with a JOIN on MAX() added on.

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

Upvotes: 1

RSCode
RSCode

Reputation: 1593

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

It will work for you

Upvotes: 2

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You need to get the latest record for every unique combination of eventdate, resource_id, added_on, like this:

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'

Upvotes: 1

Related Questions