CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 407

How to get the last reading for each day

I have this DB table in which data is constantly being added to. The Data that is being added is sensor data so there are two readings per sensor per hour. I would like to know of a way to get only the last reading of the day for the entire month. For example, 

select * from dbo.NewLogTable
where Sensor_ID= '12345'and Date_Reading between '2012-08-01' and '2012-08-31' 
and DateTimeStamp between '23:30:00'and '23:59:00'
order by DateTimeStamp asc

I know this doesn't work but its a pseudo code of what I want. Again the goal here is to get just one reading(the latest) per day for a particular sensor. I have seen plenty of example where the first and last day of the month are displayed, display past x amount of days, etc. Not really like my application. Can anyone help? thanks.

Upvotes: 0

Views: 141

Answers (5)

Zzz
Zzz

Reputation: 3025

SELECT TOP  1 * 
FROM dbo.NewLogTable
WHERE Sensor_ID = '12345'
AND Date_Reading <= '2012-08-01'
ORDER BY DateTimeSTap DESC

Upvotes: 0

Kermit
Kermit

Reputation: 34055

You can use the DATEPART function in combination with MAX to get the reading of the day for the entire month:

SELECT DATEPART(d, Date_Reading) AS day, MAX(value) AS value FROM table
WHERE Sensor_ID = '12345' 
    AND Date_Reading BETWEEN '2012-08-01' AND '2012-08-31 23:59:59'
GROUP BY DATEPART(d, Date_Reading)

Just make sure that end value of your BETWEEN has a time part.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

WITH
  sequenced AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY sensor_id, Date_Reading ORDER BY DateTimeStamp DESC) AS daily_sequence_id,
    *
  FROM
    dbo.NewLogTable
)
SELECT
  *
FROM
  sequenced
WHERE
  sensor_id = '12345'
  AND Date_Reading BETWEEN '2012-08-01' AND '2012-08-31'
  AND daily_sequence_id = 1

Upvotes: 2

podiluska
podiluska

Reputation: 51494

You can use the row_number() windowing function to achieve this

select * from
(
    select 
        *, 
        row_number() over (partition by date_reading order by datetimestamp desc) rn 
    from yourtable
) v
where rn = 1

Upvotes: 1

JonH
JonH

Reputation: 33153

You can use an aggregate, namely MAX() to handle this. Here is the MSDN:

http://msdn.microsoft.com/en-us/library/ms187751.aspx

Returns the maximum value in the expression. May be followed by the OVER clause.

Your query would result in:

SELECT SensorID, MAX(Date_reading) FROM NewLogTable GROUP BY Sensor_ID

Upvotes: 1

Related Questions