0

I have a table (in database) with one minute data in each row. Like this:

Date_time            Value

2018-11-01 10:58:00  189.0
2018-11-01 10:59:00  189.0
2018-11-01 11:00:00  189.19
2018-11-01 11:01:00  189.19
2018-11-01 11:02:00  189.35
...
2018-11-02 13:57:00  192.38
2018-11-02 13:58:00  192.41
2018-11-02 14:01:00  192.35
2018-11-02 14:02:00  192.35
...
2018-11-06 15:50:00  196.89
2018-11-06 15:51:00  196.86
2018-11-06 16:00:00  196.77
2018-11-06 16:01:00  196.69
2018-11-06 16:02:00  196.64

I need to SELECT last row of every hour in table. The difficulty here is that it is not known what minute will be the last in an hour.

  • why are you tagging this both postgresql and sqllite, which db are you using? – Evan Carroll Jan 13 at 18:10
  • Now i using SQLite. But if it is not possible to resolve the issue on SQLite, then I can go to PostgreSQL. – Denis Jan 13 at 18:18
1

According to:

 https://www.sqlite.org/windowfunctions.html

SQLite supports window functions. You can therefore easily enumerate the resultset like:

SELECT date_time, value
     , row_number() over (partition by DATE(date_time), HOUR(date_time)
                          order by dt desc) as rn
FROM T

I'm not sure what date functions there are, so you might have to change those. Now, since we ordered them in descending order, we can just pick the first one:

SELECT date_time, value
FROM (
    SELECT date_time, value
         , row_number() over (partition by DATE(date_time), HOUR(date_time)
                              order by dt desc) as rn
    FROM T
) AS X
WHERE rn = 1;
0
SELECT MAX(Date_time), Value FROM T GROUP BY STRFTIME('%Y%m%d%H', Date_time);

The STRFTIME call creates a string to represent the hour (without minutes and seconds), and the SELECT takes advantage of an SQLite-specific behavior.

Special processing occurs occurs when the aggregate function is either min() or max(). Example:

SELECT a, b, max(c) FROM tab1 GROUP BY a;

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum. So in the query above, the value of the "b" column in the output will be the value of the "b" column in the input row that has the largest "c" value. There is still an ambiguity if two or more of the input rows have the same minimum or maximum value or if the query contains more than one min() and/or max() aggregate function. Only the built-in min() and max() functions work this way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.