Eugene Bennett Eugene Bennett - 1 month ago 10
SQL Question

find single MAX value per day

I have tired using parts of my previous question which was similar , but I as the table I am querying has multiple rows I can't seem to get one single max value for the day. I will then need to merge this with the previous question, but that's another thing I need to play with...

The table is simple, 2 columns

Date | Peak Power

1/1/2012 10:00:00 | 150.00

1/1/2012 10:12:00 | 120.00

1/1/2012 05:01:00 | 190.00

1/1/2012 01:35:00 | 500.00

1/1/2012 04:54:00 | 250.00


Result

Date | Peak Power each day

1/1/2012 10:00:00 | 500.00

2/1/2012 04:00:00 | 150.00


Any help would be great.

Answer

You dont tell us what SQL product your using, so here is Sql Server 2008R2:

SELECT
CAST(t.DateColumn as DATE), MAX(t.PowerUsage) as 'Peak Power Each Day'
FROM [MyTable] t
GROUP BY CAST(t.DateColumn AS DATE)

here is MySql

SELECT
DATE(t.DateColumn), MAX(t.PowerUsage) as 'Peak Power Each Day'
FROM [MyTable] t
GROUP BY DATE(t.DateColumn)