Mysql query select every user per date latest record

Question

I am working on large amount of data.I want to select every user per date latest record . My table is given below:

 +---+--------+-----+--------+------+-----+
 |id |user_id |name |date    |time1 |time2|
 +---+--------+-----+--------+------+-----+
 |1  |1       |x    |12-1-15 |10:30 |21:30|
 |2  |1       |x    |12-1-15 |10:30 |21:30|
 |3  |2       |y    |12-1-15 |10:30 |22:30|
 |4  |1       |x    |13-1-15 |10:30 |18:30|
 |5  |2       |y    |13-1-15 |10:30 |18:30|
 |6  |2       |y    |13-1-15 |10:30 |20:30|
 +---+--------+-----+--------+------+-----+

My output will be:

 +---+--------+-----+--------+------+-----+
 |id |user_id |name |date    |time1 |time2|
 +---+--------+-----+--------+------+-----+
 |2  |1       |x    |12-1-15 |10:30 |21:30|
 |3  |2       |y    |12-1-15 |10:30 |22:30|
 |4  |1       |x    |13-1-15 |10:30 |18:30|
 |6  |2       |y    |13-1-15 |10:30 |20:30|
 +---+--------+-----+--------+------+-----+

SELECT * FROM user
Where Date In (Select Max(Date) from user 
Group by user_id,Date)

Answer 1

This query:

select
    u.*
from user u
inner join (
    select
      user_id, `date`, max(time2) as maxtime2
    from user
    group by user_id, `date`
  ) mx on u.user_id = mx.user_id and u.`date` = mx.`date` and u.time2 = mx.maxtime2

produces a result matching the expected output

| id | user_id | name |                      date | time1 | time2 |
|----|---------|------|---------------------------|-------|-------|
|  2 |       1 |    x | January, 12 2015 00:00:00 | 10:30 | 22:30 |
|  3 |       2 |    y | January, 12 2015 00:00:00 | 10:30 | 22:30 |
|  4 |       1 |    x | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  6 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 20:30 |

If just looking at date (without reference to time):

select
    u.*
from user u
inner join (
    select
      user_id, max(`date`) as maxdate
    from user
    group by user_id
  ) mx on u.user_id = mx.user_id and u.`date` = mx.maxdate

But the result from that query using the sample data is:

| id | user_id | name |                      date | time1 | time2 |
|----|---------|------|---------------------------|-------|-------|
|  4 |       1 |    x | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  5 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  6 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 20:30 |

If time does need to be accommodated then you have a design problem because you have separated the time from the date, so to arrive at the maximum date/time the columns need to be added like so:

select
    u.*
from user u
inner join (
    select
      user_id, max(addtime(`date`, time2)) as maxdatetime
    from user
    group by user_id
  ) mx on u.user_id = mx.user_id and addtime(u.`date`, u.time2) = mx.maxdatetime

and from the sample data this is the result:

| id | user_id | name |                      date | time1 | time2 |
|----|---------|------|---------------------------|-------|-------|
|  4 |       1 |    x | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  6 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 20:30 |