SQL query Question

Question

SQL query Question

Shailesh Chaudhary

Old Hand

Points: 377
More actions
June 26, 2008 at 3:44 pm

#208995

I have two tables in SQL server 2005
Sensors
SensorDetails
Sensors has two records for sensorID
0000000000805080
12345
SensorDetails has lots of records that comes online and dumped into the table e.g
SensorID InCount OutCount Date
12345 23222008-06-26 12:33:00.000
0000000000805080 6783142008-06-26 12:33:00.000
0000000000805080 6843282008-06-26 14:33:00.000
I want latest records from this table for all the sensorID. I am using the query which is giving me the above result.
select s.SensorID, sd.InCount, sd.OutCount,sd.RecTimeStamp from Sensors s join SensorDetails sd
on(s.SensorID = sd.SensorID)
where sd.RecTimeStamp in (Select MAX(RecTimeStamp) from sensors s join SensorDetails sd on
(s.SensorID = sd.SensorID) group by s.SensorID)
But I should have only two records with SensordID 12345 and 0000000000805080 . I need some generic query if more sensors are added into the table to retrieve the latest records for all the sensors.
Any help will be apprciated.
Thanks
SS

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Raghuram (AJ) Ten Centuries Points: 1214 More actions · Answer 1

Shailesh Chaudhary (6/26/2008)
I have two tables in SQL server 2005
Sensors
SensorDetails
Sensors has two records for sensorID
0000000000805080
12345
SensorDetails has lots of records that comes online and dumped into the table e.g
SensorID InCount OutCount Date
12345 23222008-06-26 12:33:00.000
0000000000805080 6783142008-06-26 12:33:00.000
0000000000805080 6843282008-06-26 14:33:00.000
I want latest records from this table for all the sensorID. I am using the query which is giving me the above result.
select s.SensorID, sd.InCount, sd.OutCount,sd.RecTimeStamp from Sensors s join SensorDetails sd
on(s.SensorID = sd.SensorID)
where sd.RecTimeStamp in (Select MAX(RecTimeStamp) from sensors s join SensorDetails sd on
(s.SensorID = sd.SensorID) group by s.SensorID)
But I should have only two records with SensordID 12345 and 0000000000805080 . I need some generic query if more sensors are added into the table to retrieve the latest records for all the sensors.
Any help will be apprciated.
Thanks
SS

Try this one (not the most efficient):

SELECT

Sd.SensorId

,SdCounts.InCount

,SdCounts.OutCount

,Sd.Date

FROM

(SELECT SensorId, MAX([Date]) As [Date] FROM dbo.SensorDetails GROUP BY SensorId) Sd

INNER JOIN

dbo.SensorDetails SdCounts

ON Sd.SensorId = SdCounts.SensorId

AND Sd.Date= SdCounts.Date

The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 2

Hi,

Just change your query slightly:

SELECT s.SensorID, sd.InCount, sd.OutCount,sd.RecTimeStamp

FROM Sensors s join SensorDetails sd on s.SensorID = sd.SensorID

WHERE sd.RecTimeStamp =

(SELECT MAX(RecTimeStamp) FROM SensorDetails WHERE SensorID=s.SensorID)

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Shailesh Chaudhary Old Hand Points: 377 More actions · Answer 3

Thanks to AJ and Hari narayan. Both queries are working good.

Thanks for your time.

//shailesh