SQL querying time data exclude other time if first time found

Question

I am trying to query data from our aspentech IP 21 server and am using the following query

SELECT s.IP_TREND_VALUE AS "Weight", s.IP_TREND_TIME AS TIMES
From "wtTotal" as s

WHERE  s.IP_TREND_TIME like '__________05:59:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00' 
OR s.IP_TREND_TIME like '__________06:00:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00'

The problem is that some days there is a data point at 5:59 and others a data point at 6:00. some have data at both 5:59 and at 6. I would like to only pull one data point for each day and never one both at 5:59 and at 6

Answer 1

with CTE as
(
SELECT s.IP_TREND_VALUE AS "Weight", s.IP_TREND_TIME AS TIMES,  
       row_number() 
         over (partition by to_char(IP_TREND_TIME, 'YYYYMMDD') 
               order by IP_TREND_TIME asc) as rn -- change the order by to change which value to select
From "wtTotal" as s
WHERE  s.IP_TREND_TIME like '__________05:59:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00' 
OR s.IP_TREND_TIME like '__________06:00:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00'
)
select *
from CTE
where RN = 1