sql - Write a query to get for each person, for each date - what is his last out_time?

Question

I have table: EMPLOYEE

EMP_ID           DATE                          IN_TIME        OUT_TIME

111                     01-Jan-2019               09:00               12:00

111                     01-Jan-2019               13:00               16:00

111                     01-Jan-2019               16:30               18:00

111                     02-Jan-2019               08:00               11:00

111                     02-Jan-2019               12:00               17:00

222                    01-Jan-2019               10:00               14:00

222                     01-Jan-2019               15:00               20:00

222                    02-Jan-2019               08:30               11:30

222                     02-Jan-2019               13:00               18:30

Write a query to get for each person, for each date - what is his last out_time?

Expected output:

EMP_ID           DATE                          IN_TIME        OUT_TIME

111                     01-Jan-2019               16:30               18:00

111                     02-Jan-2019               12:00               17:00

222                     01-Jan-2019               15:00               20:00

222                     02-Jan-2019               13:00               18:30

标签： sqlsql-server-2008

Answer 1

使用max()：

select emp_id, convert(date, out_time), max(out_time)
from t
group by emp_id, convert(date, out_time);