Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to query a record by it's highest value when grouping by hour

we have a database table where we store a new record every 30 minutes.

The data looks like this:
Image of our database structure

I wrote the following query:

SELECT *
FROM datastore_usage
WHERE datastore_id = 1
GROUP BY HOUR(created_at)
ORDER BY created_at DESC
LIMIT 10;

This query results in the following:

Current Query result

Instead of the first value of the given hour, I would like to get the record with the highest bytes_in_use of this hour.

I have tried to add the MAX(bytes_in_use) to the query. But then the value is not related to the ID and created at timestamp.

So how can I query the highest record grouped by hour?

Than you in advance.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally fixed the issue by using the following query:

    
SELECT MAX(bytes_in_use), created_at 
FROM datastore_usage 
WHERE datastore_id = 1 
GROUP BY DATE(created_at), HOUR(created_at) 
ORDER BY created_at DESC 
LIMIT 10

    This query returns the highest value of each hour. It still returns the first timestamp of the hour. But we will round this timestamp anyway.


  2. 0

    Try if it produces desired result and let me know. If yes i can explain.
    WITH CTE AS (
    SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY HOUR(created_at) ORDER BY bytes_in_use DESC) AS row_num
    FROM
    datastore_usage
    WHERE
    datastore_id = 1
    )
    SELECT
    *
    FROM
    CTE
    WHERE
    row_num = 1
    ORDER BY
    created_at DESC
    LIMIT 10;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search