mwafi mwafi - 6 months ago 26
SQL Question

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

Answer

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).

Comments