Help on whriting a SQL Script / statement

[tinojoergensen]

Hi I Have som problem whriting a SQl Script and i really apriciate if some one can help me out.

Im running a SQL 2005 Database

The table look like this, and are named DataLog

DataAndtime Value Tagname
02/11/2010 10:30:00 22 Valve
02/11/2010 10:35:00 55 P2
02/11/2010 10:40:00 54 Valve
02/11/2010 10:45:00 77 P2
02/11/2010 10:50:00 88 Valve
02/11/2010 10:55:00 12 Valve
02/11/2010 11:00:00 2 P2


I like to the dateandtime + the Max value + the Tagname back from my statement.

So something like this.


Tagname DateAndTime Value
Valve 02/11/2010 10:50:00 88
P2 02/11/2010 10:45:00 77


Could someone help me out with this.

Beste Regards
Tino Jørgensen
Denmark

[jilani]

SELECT Tagname, DateAndTime, Max(Value) FROM DataLog GROUP BY Tagname

[ggarbers]

SELECT Tagname,
MAX(DateAndTime),
MAX(Value)
FROM DataLog
WHERE Value = (Select MAX(Value)
FROM DataLog As B
WHERE B.TagName = DataLog.TagName)
GROUP BY TagName
ORDER BY TagName DESC;

[ggarbers]

This will not work because DateAndTime not in Group BY Clause.

[shubho12003]

Try this:

select DataAndtime , Value , Tagname from #temp1
where Value in (select MAX(Value) from #temp1 group by Tagname)