CODEWITHSUNDEEP
sql-server-2008
Doctorj77
Doctorj77

Reputation: 125

Show single row with most recent timestamp

Using SQL Server 2008, am trying to figure out a way to show just a single row here, that shows the greater, or the more recent of the two time stamps. Doesn't' have to match the current month either. Just the more recent one. I know how to do it if there is two separate columns, but not two rows of a single column. This is a sample data:

row Reader  Building    Timestamp       LockOut   Badge ID  Employee
1   5348      T        01/09/2015 19:08    N       F51184   35229934
2   5348      T        02/10/2015 18:23    N       F53629   38904421

I'm filtering on Reader and Building

 SELECT 
 [Reader]
,[Building]
,[Timestamp]
,[LockOut]
,[Badge ID]
,[Employee] 
FROM [Security_Sequence]
WHERE Reader = ‘5384’ AND Building = ‘T’

Expected Output

row Reader  Building    Timestamp       LockOut   Badge ID  Employee
2   5348      T        02/10/2015 18:23    N       F53629   38904421

Upvotes: 1

Views: 33

Answers (1)

Sarath Subramanian
Sarath Subramanian

Reputation: 21281

Try this if you want the latest date's record

SELECT TOP 1
 [Reader]
,[Building]
,[Timestamp]
,[LockOut]
,[Badge ID]
,[Employee] 
FROM [Security_Sequence]
WHERE Reader = ‘5384’ AND Building = ‘T’
ORDER BY [Timestamp] DESC

If you want to select latest date for each Reader and Building, you can use the below code

;WITH CTE AS
( 
     SELECT ROW_NUMBER() OVER(PARTITION BY Reader,Building ORDER BY [Timestamp] DESC) RNO,
     [Reader]
    ,[Building]
    ,[Timestamp]
    ,[LockOut]
    ,[Badge ID]
    ,[Employee] 
     FROM [Security_Sequence]         
)
SELECT 
     [Reader]
    ,[Building]
    ,[Timestamp]
    ,[LockOut]
    ,[Badge ID]
    ,[Employee] 
     FROM [Security_Sequence]
WHERE RNO = 1

Upvotes: 1

Related Questions