(SQL Server 14.0.3421.10 using Microsoft SQL Server ) I know you guys are always helpful, in this case I don't even know how to google because SQL is not my cup of tea. Code as below
declare @refdt as datetime
declare @loaddt as datetime
set @refdt='2022-04-01 01:00:00:000'
set @loaddt='2022-04-01 02:00:00:000'
select dt, id, Unit, MIN(pmVoltage) as[test]
from energymeter15min
where (id LIKE '3_4237') and (dt BETWEEN @refdt and @loaddt)
group by dt, Unit, pmVoltage, id
order by Unit, dt
Result of this query looks like this
But I simply want to display the MIN value of pmVoltage in the timespan between @refdt and @loaddt like this
CodePudding user response:
Give this a try and let me know if it works. It should only display one record for each Unit/id combination which has the lowest pmVoltage value.
declare @refdt as datetime;
declare @loaddt as datetime;
set @refdt='2022-04-01 01:00:00:000';
set @loaddt='2022-04-01 02:00:00:000';
With data AS(
select dt, id, Unit, pmVoltage test
, ROW_NUMBER() OVER(Partition BY Unit, id ORDER BY pmVoltage) rowNum
from energymeter15min
where id = '3_4237'
and dt BETWEEN @refdt and @loaddt
)
select dt, id, Unit, test
from data
where rowNum = 1
CodePudding user response:
Try taking pmVoltage out of your GROUP BY - the minimum is determined by the group by, it should not be in it.
group by dt, Unit, id
order by Unit, dt