问题描述:

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

网友答案:

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

网友答案:

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;
网友答案:

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

相关阅读:
Top