返回所有字段时，在列中选择唯一值

SELECT tblDTR.emp_id,
       tblEmployee.first_name,
       tblEmployee.last_name,
       tblDTR.time_out,
       tblDTR.time_in
FROM   tblDTR
INNER JOIN
       tblEmployee
ON     tblDTR.emp_id = tblEmployee.emp_id
WHERE  (((tblDTR.current_date)=#9/3/2015#));

I just want the records with only unique IDs and the earliest time_in value, e.g.:

我只想要只有唯一ID和最早time_in值的记录,例如:

EMP1234 - 12:00 AM
EMP12 - 12:45 AM
EMP1 - 7:30 AM (not 12:50 PM)

...in order by time in.

...按时间顺序排列。

1 个解决方案

#1

Use NOT EXISTS to return a row only if no older row with same emp_id exists:

仅当不存在具有相同emp_id的旧行时,才使用NOT EXISTS返回行:

SELECT t1.emp_id,
       tblEmployee.first_name,
       tblEmployee.last_name,
       t1.time_out,
       t1.time_in
FROM   tblDTR t1
INNER JOIN
       tblEmployee
ON     t1.emp_id = tblEmployee.emp_id
WHERE  (((t1.current_date)=#9/3/2015#))
and NOT EXISTS (select 1 from tblDTR t2
                where t2.emp_id = t1.emp_id
                  and t2.time_in < t1.time_in)