Filter the lowest values of a sql result every time a column changes-CodePudding

Sorry for weird title its hard for me to explain what i need but i will do my best,

My table BehaviorHidraulics contains these columns: [id],[siteIDDatagate],[datetime],[value],[channelnum],[channeltype], now i need to find the lowest value of [value] in a certain date and i've managed to do so with this query:

select  min(value) as minvalue, siteIDDatagate, channeltype, channelnum, datetime 
  from BehaviorHidraulic where channelnum = '1' and datetime 
  between '2021-10-10 00:00:00' and '2021-10-10 23:59:59' 
  group by siteIDDatagate, channeltype, channelnum, datetime order by siteIDDatagate, minvalue ASC

now this returns something like this:

minvalue  siteIDDatagate
26         _site_1003
26,39      _site_1003 
26,5       _site_1003 
17,20      _site_303
17,600     _site_303
58,200     _site_304
58,599     _site_304

and this is good but i need to ditch every result under the first row for every site so it could look like this:

minvalue  siteIDDatagate
26         _site_1003
17,20      _site_303
58,200     _site_304

i think i need to use the lag function but im not very good with SQL so please any help is greatly appreciated

CodePudding user response：

I don't think the lag function will help you in this case. The lag function is a windowed function that allows you to access data in a previous row. Akina, posted what I think is the answer for you.

Currently your query is:

select  min(value) as minvalue, siteIDDatagate, channeltype, channelnum, datetime
  from BehaviorHidraulic where channelnum = '1' and datetime 
  between '2021-10-10 00:00:00' and '2021-10-10 23:59:59' 
  group by siteIDDatagate, channeltype, channelnum, datetime order by siteIDDatagate, minvalue ASC

The additional "group by" fields that you have are causing the excess rows. Your results seem to only care about min(value) and siteIDDatagate yet you're grouping on additional fields unnecessarily. You should consider rewriting the query as such:

select  min(value) as minvalue, siteIDDatagate
  from BehaviorHidraulic where channelnum = '1' and datetime 
  between '2021-10-10 00:00:00' and '2021-10-10 23:59:59' 
  group by siteIDDatagate order by siteIDDatagate, minvalue ASC

This will ensure that you only retrieve the minimum value over the date period specified for each siteIDDatagate.

If you shave other requirements, please specify.

CodePudding user response：

You can use row_number() window function to find the first record (the one with minimum value). Here an is an exmaple

;with cte AS
(
  select siteIDDatagate, 
         channeltype, 
         channelnum, 
         datetime,
         row_number() over(PARTITION BY siteIDDatagate, channeltype, channelnum ORDER BY value) AS RN
  from BehaviorHidraulic where channelnum = '1' 
       and datetime between '2021-10-10 00:00:00' and '2021-10-10 23:59:59' 
)
SELECT *
FROM cte 
WHERE RN = 1