SQL MAX without time question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL MAX without time question

Author

Topic

cwestern
Starting Member

7 Posts

Posted - 2011-06-21 : 11:11:57

I am trying to get a max temperature for a sensor. The problem is that it returns multiple values when I only want on record for today with the maximum temperature. It returns a date and time. I converted the column to only show the date. Could this be my problem?

Here's my statement...


SELECT  SensorID
      , TIME = CONVERT(CHAR(10), CollectionTime, 101)
      , MAX(Temperature) AS MAXTemp, MIN(Temperature) AS MINTemp
FROM    dbo.EMCData
WHERE   CONVERT(CHAR(10), CollectionTime, 101) = '06/21/2011'
GROUP BY CollectionTime, SensorID, Temperature

cwestern
Starting Member

7 Posts

Posted - 2011-06-21 : 11:18:44

Here's what my table grabs.. There are 10 sensors. I just want to grab the max temperature for each sensor 1-10..

for example sensor 1 returns multiple results

ID Date MAX MIN
1 06/21/2011 81.68000 81.68000
1 06/21/2011 81.86000 81.86000
1 06/21/2011 81.86000 81.86000
1 06/21/2011 81.68000 81.68000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 11:26:10

Remove the Temperature column from the group by clause and use the exact same time conversion in the group by clause that you have in the select clause:


SELECT  SensorID
      , TIME = CONVERT(CHAR(10), CollectionTime, 101)
      , MAX(Temperature) AS MAXTemp, MIN(Temperature) AS MINTemp
FROM    dbo.EMCData
WHERE   CONVERT(CHAR(10), CollectionTime, 101) = '06/21/2011'
GROUP BY CONVERT(CHAR(10), CollectionTime, 101), SensorID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 11:27:47

SELECT SensorID
, TIME = CONVERT(CHAR(10), CollectionTime, 101)
, MAX(Temperature) AS MAXTemp, MIN(Temperature) AS MINTemp
FROM dbo.EMCData
WHERE CONVERT(CHAR(10), CollectionTime, 101) = '06/21/2011'
GROUP BY CONVERT(CHAR(10), CollectionTime, 101), SensorID

I take it you want to get rid of the where clause?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cwestern
Starting Member

7 Posts

Posted - 2011-06-21 : 11:28:48

That worked! Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources