SELECT Date_Time, Machine_Name, UsageValue
FROM (SELECT *, ROW_NUMBER() OVER(Partition by DAY(Date_Time), machine_name ORDER BY usageValue DESC) rn
FROM monthlyData
) t
WHERE rn=1
ORDER BY Date_Time
SELECT Date_Time, Machine_Name, UsageValue
FROM (SELECT *, ROW_NUMBER() OVER(Partition by DAY(Date_Time), machine_name ORDER BY usageValue DESC) rn
FROM monthlyData
) t
WHERE rn=1
ORDER BY Date_Time
-- Chandu
though that might work for sample data given, its not correct add the below sample data and you'll see why
2/1/12 01:00:00 T21 12 2/1/12 01:00:00 T21 30
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
SELECT Date_Time, Machine_Name, UsageValue
FROM (SELECT *, ROW_NUMBER() OVER(Partition by DATEDIFF(dd,0,Date_Time), machine_name ORDER BY usageValue DESC) rn
FROM monthlyData
) t
WHERE rn=1
ORDER BY Date_Time
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Finding Maximum Value for each machine day wise.
Posted - 01/28/2013 : 20:26:58
