CODEWITHSUNDEEP
phpmysqlsqldatabase
CyberJunkie
CyberJunkie

Reputation: 22674

MYSQL select oldest record nearest to given date but not older

I have a table with records belonging to different users.

Table:

RECORD_ID     USER_ID     DATE_CREATED
1             5           08/05/12
2             5           08/04/12
3             10          08/05/12
4             10          08/02/12
5             10          08/01/12

Query:

SELECT user_id, MIN(date_created) as date_created
FROM (entries) 
GROUP BY user_id

The query returns the oldest record for each users.

RECORD_ID     USER_ID     DATE_CREATED
2             5           08/04/12
5             10          08/01/12

How can I select the oldest record nearest to a given date but not older? Something along the lines of...

SELECT oldest record > given oldest record
WHERE oldest record NOT =< given oldest record

For example a query returns all records for user 10:

RECORD_ID     USER_ID     DATE_CREATED
3             10          08/05/12
4             10          08/02/12
5             10          08/01/12

The given date for user 10 is 08/02/12. Now I have to select the next oldest record above 08/02/12.

That would be 08/05/12 and never records below that unless it reached the top. How is this possible in MYSQL?

Upvotes: 2

Views: 2583

Answers (4)

valex
valex

Reputation: 24134

Here is the query to select nearest records for all users with record_id's. Just change '08/02/12' to date you need:

select 
    entries.record_id,entries.user_id, entries.Date_created 
from entries
join
(select 
      user_id,min(Date_created) minDate 
           from entries 
           where Date_Created>'08/02/12' 
      group by user_id
) minDates on (entries.user_id=minDates.user_id) 
              and 
              (entries.Date_created=minDates.minDate)

Upvotes: 0

iWantSimpleLife
iWantSimpleLife

Reputation: 1954

How about

Select user_id, date_created from entries where date_created > somedate order by date_created desc limit 1

Upvotes: 2

abhshkdz
abhshkdz

Reputation: 6365

Let $date_given have the given date.

SELECT user_id, MIN(date_created) as date_created
FROM (entries) 
WHERE date_created > $date_given
GROUP BY user_id
LIMIT 1

Upvotes: 2

John Conde
John Conde

Reputation: 219794

You can do this with a combination of WHERE and LIMIT:

SELECT user_id, content, MIN(date_created) as date_created
FROM (entries) 
WHERE date_created > '2012-08-02'
GROUP BY user_id
LIMIT 1

There WHERE statement will handle the date issue. The LIMIT statement will keep your results down to one row. Obviously you can change 2012-08-02' to be a variable or whatever date you need.

Upvotes: 2

Related Questions