ron ron - 2 years ago 88
SQL Question

Select max date and max time from query

This is my current data in database.

========================================
id | IC |date |time |
1 | test |2017-07-27 |14:19:26 |
2 | test |2017-07-27 |14:20:26 |
3 | second |2017-07-28 |06:58:55 |
========================================


I want to get the maxdate and maxtime for each IC.

I tried:

SELECT id,pass_no,time_in,ic,date_in FROM `check_in`
WHERE date_in = (SELECT MAX(date_in) FROM check_in)
AND
time_in = (SELECT MAX(time_in) FROM check_in) GROUP BY IC


But it only return the last row data for me. The result I wanted is like

========================================
id | IC |date |time |
2 | test |2017-07-27 |14:20:26 |
3 | second |2017-07-28 |06:58:55 |
========================================

Answer Source

You can use a tuple and group by ic

  SELECT id,pass_no,time_in,ic,date_in 
  FROM `check_in` 
  WHERE (date_in, time_in, ic) in  (
        SELECT MAX(date_in), max(time_id), ic 
          FROM check_in
          GROUP BY id) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download