question

bobargo1 avatar image
bobargo1 asked

Need help with a query to select the max date record per hour

Hello All, Would appreciate greatly if anyone helps me out to build a select from a table by DATE column. What I'm trying to select is similar to trading charts of different periods. My raw data has records datestamped per second. I'm trying to select a single record per hour with maximum value withing that our. Thanks a lot guys in advance. Help a newbie. Best regards, Bob
timestamp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
something like: WITH myCTE AS (SELECT myFields, ROW_NUMBER() OVER (PARTITION BY dateadd(hour, datediff(hour, '20100101', myDate), '20100101' ORDER BY MyValue DESC) AS rownum FROM myTable .....) SELECT * FROM myCTE WHERE rownum = 1 untried, untested, but should get you going in the right direction... For more about ROW_NUMBER: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 For more about CTEs: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 For more about the dateadd/datediff thing: https://www.google.co.uk/search?q=dateadd+datediff+remove+time
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@bobargo1 Alternatively, you can group by that dateadd datediff combination which @ThomasRushton ♦♦ suggested. What this combination does is it selects the datetime value for the hour of the given input value dropping the minutes, seconds and milliseconds parts. This is the single most efficient way to get the date values such as start of day, start of hour, year, month, week etc. For example, suppose there is a table with 3 columns:Ticker, Price and TradingPeriod (date and time of the trading day). The trading data is available for every second. Applying the dateadd datediff and grouping by it will let you select, say, hour high and hour low for each ticker. Something like this: select Ticker, min(Price) HourLow, max(Price) HourHigh, dateadd(hour, datediff(hour, 0, TradingPeriod), 0) TradingHour from YourTable group by Ticker, dateadd(hour, datediff(hour, 0, TradingPeriod), 0) order by Ticker, TradingHour; If you have SQL Server 2012 or better you can also include the moving average calculation to the equation, something like 10-hour SMA, 50-hour SMA etc.
1 Like 1 ·
Why didn't I think of that first off? Probably because I've been explaining this technique elsewhere this week... :-/
0 Likes 0 ·
Gentleman, My raw data is very not consistent. Some hours are missing at all, some have more than 3600 records, some less. I put together this but it's running forever: select Price, Timestamp from My_table where Timestamp in ( select max(Timestamp) from My_table group by date(Timestamp),hour(Timestamp) ) Can't figure how to moush DATEADD and DATEDIFF into it.
0 Likes 0 ·
@bobargo1 The SQL statement in your comment is NOT a valid T-SQL script, so if you say that it runs then it means that you are not using SQL Server. From the functions you use it looks like you have MySQL database, which is extremely limited in SQL features availability comparing to SQL Server and Oracle. Also, the script does not appear to return the maximum price, it rather looks like it is trying to get the ***latest*** available price for each hour, that is all. Is this what actually needs to happen? You just need the latest price for each hour? Please clarify. The latest version of MySQL, which became available yesterday, April 19 2018, does have support for windowing functions, but the odds are that you don't have this version available. Please clarify the expected results, there should be some way to get them, even in MySQL.
0 Likes 0 ·
@Oleg I apologize for not being clearly specific. You are absolutely correct. I'm running it on MySQL, not the latest. Version 5.6.16-1~exp1 And yes, I'm trying to select the latest price within each hour available in my table.
0 Likes 0 ·
Show more comments
bobargo1 avatar image
bobargo1 answered
@Oleg I tried to build an inner join but getting this frustrating error: Unknown column 'h.Timestamp' in 'on clause I'm testing the script on a table worth just 3 months. Number of records per second is inconsistent. For some range I have a single records per minute, some ranges are a record per second, small random time gaps all through the table. Long story short - A mess :)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bobargo1 avatar image
bobargo1 answered
@Oleg I'm thinking it must be MySQL limitation: Here is the script you helped me with and still getting the error: Unknown column 'h.ts' in 'on clause' select t.Price, t.Timestamp from Table as t inner join ( select max(Timestamp) from Table group by date(Timestamp), hour(Timestamp)) as h on t.Timestamp = h.Timestamp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bobargo1 avatar image
bobargo1 answered
@Oleg, SOrry, I missed LastOfHour and set it as h.LastOfHour. But I get only one record. Let me try to tweak it. :)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.