How to get the nTH row in ordered result set?

Question

I have the follwoing result set from the table PUNCHREPORT

EMPLID   Name    PUNCHDATE    PUNCHTIME             DEVICETYPE
..................................................................

70       GT     2014-05-28    07:46:53.0000000      IN          
70       GT     2014-05-28    07:46:58.0000000      IN  
70       GT     2014-05-28    07:47:50.0000000      IN          
70       GT     2014-05-28    12:16:19.0000000      OUT   
70       GT     2014-05-28    12:17:55.0000000      OUT         
70       GT     2014-05-28    12:24:27.0000000      IN          
70       GT     2014-05-28    16:32:57.0000000      OUT
70       GT     2014-05-28    16:33:57.0000000      OUT
70       GT     2014-05-28    16:33:59.0000000      OUT
72       MS     2014-05-28    07:47:50.0000000      IN          
72       MS     2014-05-28    12:16:19.0000000      OUT 
88       ZE     2014-05-28    07:47:50.0000000      IN          
88       ZE     2014-05-28    07:48:40.0000000      IN          
88       ZE     2014-05-28    12:16:19.0000000      OUT 

I need the above should be result as follow:

EMPLID   Name    PUNCHDATE    PUNCHTIME             DEVICETYPE
..................................................................

70       GT     2014-05-28    07:47:50.0000000      IN          
70       GT     2014-05-28    12:17:55.0000000      OUT         
70       GT     2014-05-28    12:24:27.0000000      IN          
70       GT     2014-05-28    16:33:59.0000000      OUT
72       MS     2014-05-28    07:47:50.0000000      IN          
72       MS     2014-05-28    12:16:19.0000000      OUT         
88       ZE     2014-05-28    07:48:40.0000000      IN          
88       ZE     2014-05-28    12:16:19.0000000      OUT 

Means for the every occurence of DEVICETYPE the latest row will be taken

Answer 1

To get that every row need to know the value of the next one, but the LEAD function is not in SQLServer 2008, so an auto-join is used in his stead

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID = Row_Number() OVER (PARTITION BY EMPLID 
                                 ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
)
Select a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
FROM   I a
       LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID = a.ID - 1
WHERE  a.DeviceType <> b.DeviceType 
   OR  a.ID = 1
ORDER BY a.EMPLID, a.PUNCHDATE, a.PUNCHTIME

SQLFiddle demo

The Row_Number is used to simplify the JOIN condition.