Let's say i have a table like this:
id | date | price
-------------------------------
1 | 2018-03-06 22:19:10 | $10
2 | 2018-03-06 13:16:19 | $12
3 | 2018-03-06 00:12:11 | $18
4 | 2018-03-05 23:29:10 | $10
5 | 2018-03-05 03:16:19 | $05
6 | 2018-03-05 00:11:11 | $11
I want to retrieve distinct date , and for each distinct date its first result (like lowest hour) and its related price and the latest result (like highest hour) and its related price. I also need to retrieve the highest and lowest price for each distinct date.
Is it possible doing this with a single query? If yes, how? What I tried is using distinct(date) but, since there are different hours, it returns all the results. I also tried Trunc but does not work.
Sample result:
date min_price max_price lowest_hour_price highest_hour_price
2018-03-06 $10 $18 $18 $10