Getting first row by employee for latest date

[ghbeers]

Although I am using what I thought was the correct syntax, I am getting an error.

I have a table like

emp id status date time

An employee can have multiple records. I want the status of the latest date and time. But ordering this by descending date and time is throwing an error. I'm using sql management studio to develop the query.

Can someone help me with this? Thank you.

 

[dhookom]

dhbeers,
Is "emp id status date time" 3, 4, or 5 fields? Can you provide the actual field names and types? Some sample data and desired results would also help.

Duane
Hook'D on Access
MS Access MVP

 

[ghbeers]

Below is the table I am working with. I want to order by descending date and time and return the first row.

KEY | EMP ID | DOC | STATUS | DATE | TIME
12232 | 54146 | 1095C | C | 11/25/15 | 15:32
12233 | 34108 | 1095C | C | 11/19/15 | 10:55
12234 | 34108 | 1095C | W | 11/18/15 | 10:55
12235 | 54146 | 1095C | W | 11/18/15 | 13:55
12236 | 54146 | 1095C | C | 11/17/15 | 13:55
12237 | 54146 | 1095C | W | 11/17/15 | 13:56

The results I want:
-------------------

EMP ID | STATUS | DATE | TIME
54146 | C | 11/25/15 | 15:32
34108 | C | 11/19/15 | 10:55

 

[ghbeers]

I have resolved this problem by using the following syntax:

select H.HRPER_ID, DH1.DCHIST_STATUS, DH1.DCHIST_STATUS_DATE, DH1.DCHIST_STATUS_TIME
from HRPER H
join (select DH.DCHIST_PERSON_ID, DH.DCHIST_STATUS, DH.DCHIST_STATUS_DATE, DH.DCHIST_STATUS_TIME,
Row_Number() over (partition By DH.DCHIST_PERSON_ID order by DH.DCHIST_STATUS_DATE + DH.DCHIST_STATUS_TIME DESC) as RowId
from DOC_CONSENT_HISTORY DH
where DH.DCHIST_DOCUMENT = '1095C' ) DH1
on H1.DCHIST_PERSON_ID = H.HRPER_ID
and RowId = 1

Thank you to anyone who has responded.