CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

MySQL: select closest date before specific date

How to select only closest date (select full row) before specific date for each user_id? (select single row for each user)

table sample:

id     user_id     create_date
0         1         2016-01-01
1         1         2016-01-05
2         2         2016-01-06
3         3         2016-01-06
4         2         2016-01-09
5         2         2016-01-12
6         4         2016-01-15
7         1         2016-01-21
8         2         2016-01-22
9         5         2016-01-25

SQL sample: (seems not working as expected)

select t.id,t.user_id,MAX(t.create_date)
from sample_table t
where date(t.create_date) < '2016-01-20'
group by t.user_id
limit 10

expected result:

id     user_id     create_date
1         1         2016-01-05
3         3         2016-01-06
5         2         2016-01-12
6         4         2016-01-15

Upvotes: 0

Views: 3579

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

In your sample data, the ids increase as the create_date increases. This seems reasonable, and if true, you can use:

select max(t.id) as id, t.user_id, MAX(t.create_date)
from sample_table t
where date(t.create_date) < '2016-01-20'
group by t.user_id;

I don't think the date() function is needed in the where clause; that prevents an index from being used (if appropriate).

Upvotes: 2

Taher Rahgooy
Taher Rahgooy

Reputation: 6696

You can use nested selects:

select * from 
     (select * from sample_table where 
          date(create_date) < '2016-01-20' 
          order by user_date desc 
     )t 
group by user_id

here is a fiddle

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5432

You could use

select 
    t.id, t.user_id, t.create_date
from 
    sample_table t
inner join
    (
        select 
            user_id, MAX(create_date) max_create_date
        from 
            sample_table 
        where 
            date(create_date) < '2016-01-20';
        group by 
            user_id
    ) t1
on
    t.user_id = t1.user_id and t.create_date = t1.max_create_date;

Upvotes: 1

Related Questions