Find the minimum flow in a day and the time it occurs

using MSQL 2005

I have a continuous set of flow measurements (averaged for each 15 minute time slice)

I am trying to write a query to find the minimum flow for each day and the time it occurs Finding the minimum flow is easy but getting the time is harder.

Currently I do this:

select d1.data_point_groupid
     , min(d1.timeID) [timeId]
     , min(d1.[value]) [value] 
from dma.dbo.calculated_average_group_flow d1
where night=1 and round(d1.value, 6)=
(
  开发者_如何学Python  select round(min(value), 6)
     from dma.dbo.calculated_average_group_flow d2
     where night=1
        and d2.[date]=d1.[date]
        and d2.data_point_groupid=d1.data_point_groupid
)
group by d1.data_point_groupid, d1.date

However this will occasionally not match due to rounding errors

I have also tried using ranking but this is so slow I had to cancel the query

select [data_Point_GroupID], [date], [timeId], [value] from
    (
    select * , Rank() over (Partition BY data_Point_GroupID, [date] order by value ASC) as Rank
    from 
    [calculated_average_group_flow] d2
    ) d1
 WHERE rank=1

The calculated_average_group_flow is another view that does the averaging calculations

Is there a better way to do this?

When comparing floating point, you need to use an epsilon (I used 1e-9 below) to avoid precision errors:

select d1.data_point_groupid
     , min(d1.timeID) [timeId]
     , min(d1.[value]) [value] 
from dma.dbo.calculated_average_group_flow d1
where night=1 and 1e-9 >=
(
    select abs(d1.value - min(d2.value))
     from dma.dbo.calculated_average_group_flow d2
     where night=1
        and d2.[date]=d1.[date]
        and d2.data_point_groupid=d1.data_point_groupid
)
group by d1.data_point_groupid, d1.date

you need to min the value first, then find the time related to that min value in an outer query. I'm not sure why you're rounding the value.

select d.data_point_groupid, min_value, timeId 
from dma.dbo.calculated_average_group_flow d inner join
       (select data_point_groupid, min([value]) as min_value
       from dma.dbo.calculated_average_group_flow 
       where night=1 
       group by data_point_groupid) mnv on 
d.data_point_groupid = mnv.data_point_groupid and 
d.[value] = mnv.min_value
where night=1