Search code examples
sqlsql-servert-sqlsql-order-by

Sorting SQL query from timestamp, id and Unit

(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

enter image description here

But I simply want to display the MIN value of pmVoltage in the timespan between @refdt and @loaddt like this

enter image description here

Solution

  • 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 LIKE '3_4237' 
  and   dt BETWEEN @refdt and @loaddt
)
select dt, id, Unit, test 
from  data
where rowNum = 1