GROUP BY day but also show time on day-CodePudding

I have price-table with TIMESTAMPs and PRICE spanning over 3 years. I would like GROUP these with MAXIMUM price per day but also show which time on the day the MAXIMUM price occured.

This is my SQL:

SELECT Date(TIMESTAMP), MAX(PRICE)
    FROM history
    WHERE Currency = 'USD'
GROUP BY Date(TIMESTAMP)
ORDER BY MAX(PRICE) DESC

Result:

How can I GROUP by the DATE (YYYY-MM-DD) but also show the time on the day when the MAXIMUM price occured? (The time is included in the TIMESTAMP)

CodePudding user response：

The cleanest way to use window functions and you won't need cte or joining table twice:

select * from history
where Currency = 'USD'
qualify row_number() over (partition by date(TIMESTAMP) order by price desc) = 1
order by price desc

CodePudding user response：

You can use CTE in this challange:

WITH max_price AS (
    SELECT DATE(timestamp) max_price_date, MAX(price) max_price
    FROM history
    WHERE Currency = 'USD'
    GROUP BY Date(timestamp)
) 
SELECT * 
FROM max_price 
JOIN history ON history.price = max_price.max_price AND DATE(history.timestamp) = max_price_date
ORDER BY max_price DESC

MySQL 8 fiddle

CodePudding user response：

If your database server supports it you can use the FIRST_VALUE function grouping first by the date (and currency), and ordering the rows within the group by decreasing price:

SELECT cast([TIMESTAMP] as date), MAX_PRICE_INDAY_REACHED_TIMESTAMP, MAX(PRICE)
FROM (
     select 
         [TIMESTAMP], PRICE, CURRENCY, 
         MAX_PRICE_INDAY_REACHED_TIMESTAMP=FIRST_VALUE([TIMESTAMP]) OVER (PARTITION BY  cast([TIMESTAMP] as date), Currency ORDER BY PRICE DESC)
     from history
     ) as h
WHERE Currency = 'USD'
GROUP BY cast([TIMESTAMP] as date), MAX_PRICE_INDAY_REACHED_TIMESTAMP
ORDER BY MAX(PRICE) DESC