SQL want to get the unique high price for every date including time-CodePudding

 select distinct 
     date, max(high) 
 from 
     `eurusdm1.eurdata112021.forexdata` 
 group by 
     date

Output from current query:

1    2021-08-06     1.17636
2    2021-08-09     1.17689
3    2021-08-10     1.17428
4    2021-08-11     1.17533

I am trying to add a time column to this table that is located in the table, but I am not sure how to accomplish this, so I just want to display the time field named time, along with this query here. This is just for a Euro/U.S. Dollar currency query to get the time that the high price was reached during the day, since the max(high) only happens once per day.

CodePudding user response：

We can use ROW_NUMBER with QUALIFY here:

SELECT date, time, high
FROM eurusdm1.eurdata112021.forexdata
WHERE TRUE
QUALIFY ROW_NUMBER() OVER (PARTITION BY date ORDER BY high DESC) = 1;