使用MAX和GROUP BY从表中提取数据

Question

I'm trying to pull data out from a table. To simplify I have a table (time_entries ) has 3 colums user_name, entry_type and entry_datetime

Here is a sample output

user_name| entry_type   | entry_datetime
 User1   |   Time In    | 28-JUL-13  16:40:40
 User1   |   Time Out   | 28-JUL-13  16:40:41
 User2   |   Time In    | 28-JUL-13  16:41:13
 User2   |   Time Out   | 28-JUL-13  16:41:15
 User3   |   Time In    | 28-JUL-13  16:42:32

What I'm trying to do here is to pull the result when the last time each User logged in

MY QUERY

SELECT te.user_name, te.entry_type,  MAX(te.entry_datetime) AS date
FROM time_entries AS te
GROUP BY te.user_name 

this runs fine only with wrong results, here is the output below

OUTPUT

user_name| entry_type | entry_datetime
User1    | Time In    | 28-JUL-13 16:40:41
User2    | Time In    | 28-JUL-13 16:41:15
User3    | Time In    | 28-JUL-13 16:42:32

user_name and entry_datetime is correct but the entry_type are all Time In. User1 and User2 entry_type must be Time Out.

Anyone knows a solution for this problem?

Answer 1

You can use a filtering join to list the latest entry per user:

select  *
from    time_entries te
join    (
        select  user_name
        ,       max(entry_datetime) as maxdt
        from    time_entries
        group by
                user_name
        ) filter
on      filter.user_name = te.user_name
        and filter.maxdt = te.entry_datetime

Working example at SQL Fiddle.