CODEWITHSUNDEEP
sql
Marty Cochrane
Marty Cochrane

Reputation: 689

sql select latest value

I have the following SQL statement

SELECT [Motor]
      ,[Time]
  FROM [logger].[dbo].[motor]
  WHERE day([Time]) = day(getdate())
  ORDER BY [TIME]

This is a very basic table that gives me the status of a motor at a given time.

All I want is a select statement that will give me the latest result if possible. I can get it so it gives all of today's results but if I could get it so that it only displays the latest result that would be great.

Upvotes: 0

Views: 475

Answers (3)

Marc Gravell
Marc Gravell

Reputation: 1062745

The "latest" is TOP 1 and ORDER BY [x] DESC - however, I don't think the query is right; day returns the day of the month, not the date-only part of a datetime:

DECLARE @today datetime = CAST(FLOOR(CAST(getdate() as float)) as datetime)
DECLARE @tomorrow datetime = @today + 1

SELECT TOP 1 [Motor], [Time]
FROM [logger].[dbo].[motor]
WHERE [Time] >= @today AND [Time] < @tomorrow
ORDER BY [Time] DESC

Note I've chosen to preserve your "today" semantics; this could matter if you have future data.

Upvotes: 2

Mongus Pong
Mongus Pong

Reputation: 11477

SELECT TOP(1) [Motor] ,[Time] 
FROM [logger].[dbo].[motor] 
WHERE day([Time]) = day(getdate()) 
ORDER BY [TIME] DESC

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453142

To "only display the latest result" you would just use TOP

SELECT TOP (1) [Motor] ,[Time] 
FROM [logger].[dbo].[motor] 
ORDER BY [TIME] desc

Or (if you wanted the TOP 1 for each motor)

WITH cte As
(
SELECT  [Motor] ,
        [Time], 
        ROW_NUMBER() OVER (PARTITION BY [Motor] ORDER BY [TIME] DESC) RN
FROM [logger].[dbo].[motor] 
)
SELECT [Motor] ,[Time]
FROM cte
WHERE RN=1

Upvotes: 5

Related Questions