Select unique values in columns while returning all fields

Question

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.:

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

...in order by time in.

You know most of the potential answerers react allergic to begging and shouting? — Deduplicator, Sep 08 '15 at 10:14
the first time is Ascending order only @T.J.Crowder for a employee with two in records :) thank you for your reponse — therazerme, Sep 08 '15 at 10:17
@T.J.Crowder i want always the earlier record on EMP with multple record .. only 1 record for each ID even though an EMP_ID can have many record of the ... the query must always select the earliest one — therazerme, Sep 08 '15 at 10:29
This is a duplicate of [this question](http://stackoverflow.com/questions/1895110/row-number-in-mysql). bobince's answer there does what you want, you just change the `>` to a `<` (and of course, the column names). — T.J. Crowder, Sep 08 '15 at 10:38

score 0 · Accepted Answer · answered Sep 08 '15 at 14:02

Use NOT EXISTS to return a row only if no older row with same emp_id 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)

Thank.. you :) This sql made it :) – therazerme Sep 08 '15 at 17:27 — therazerme, Sep 08 '15 at 17:27

Select unique values in columns while returning all fields

1 Answers1