CODEWITHSUNDEEP
mysqlsqltime
sleepsleepsleep90731
sleepsleepsleep90731

Reputation: 959

Get most recent older value from one hour ago in mysql

I want to get the most recent older value from one hour ago in my data but when I execute this query 

SELECT locid, value
FROM  table2
WHERE date_sub(t_stamp, interval 1 hour) 
and locid = '2815'
order by t_stamp desc

I get all the values. How can I fix this?

The output should be

locid  | value
2815   | 13.0

Here's the demo: http://sqlfiddle.com/#!2/b3c89/5

Upvotes: 2

Views: 539

Answers (3)

Linger
Linger

Reputation: 15058

The Following query will get the current hour and subtract one from it and then compare it to the hour of t_stamp.

SELECT locid, value
FROM  table2
WHERE DATE(t_stamp) = DATE(CURRENT_DATE)
AND HOUR(t_stamp) = HOUR(CURRENT_TIME) -1
AND locid = '2815'
ORDER BY t_stamp desc
LIMIT 1

Or the following query might be what you are looking for:

SELECT locid, value
FROM  table2
WHERE t_stamp <= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND locid = '2815'
ORDER BY t_stamp desc
LIMIT 1

Upvotes: 4

Davis Yaregu
Davis Yaregu

Reputation: 41

Use LIMIT 1 to return only the top value

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Simply add a LIMIT to your query to return only the first row.

SELECT locid, value
FROM  table2
WHERE date_sub(t_stamp, interval 1 hour) 
and locid = '2815'
order by t_stamp desc limit 1

Upvotes: 0

Related Questions