Select max date and max time from query

Question

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   |
    ========================================

See this link already asked by some one. https://stackoverflow.com/questions/22629307/mysql-select-where-max-date-and-max-time — Zaid Bin Khalid, Jul 27 '17 at 07:06

score 2 · Answer 1 · answered Jul 27 '17 at 07:06

2

This will return the max date & time per ic:

select ic, max(date), max(time)
from check_in
group by ic

answered Jul 27 '17 at 07:06

Carra

17,808
7
62
75

but this return id 1 and 3 i tried, i want id 2 and 3 – ron Jul 27 '17 at 07:12
@ron Why didn't you described it correctly in your question? It just tells: "I want to get the maxdate and maxtime for each IC." Nothing about id. – Vadym Pechenoha Jul 27 '17 at 07:14

score 0 · Answer 2 · answered Jul 27 '17 at 07:07

0

This should work

 select max(time),date,id 
 from (select max(date) as date,time,id from check-in group by time,id ) 
 group by date,id;

answered Jul 27 '17 at 07:07

pritesh agrawal

1,155
8
16

score 0 · Accepted Answer · answered Jul 27 '17 at 07:08

0

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)

answered Jul 27 '17 at 07:08

ScaisEdge

131,976
10
91
107

nice your answer works! – ron Jul 27 '17 at 07:19

score 0 · Answer 4 · answered Jul 27 '17 at 07:09

0

Did you try with "OR" condition insteads "AND"?

answered Jul 27 '17 at 07:09

Luong.Khuc

26
3

Select max date and max time from query

4 Answers4