Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Sql query to find max value within 60 seconds ....
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yasinirshad
Starting Member

18 Posts

Posted - 10/19/2016 :  02:04:23  Show Profile  Reply with Quote
Hi,

Suppose i have a table like below (with different ids) ... here for example took '99' ...
id hist_timestamp DP mints Secnds value
99 2016-08-01 00:09:40 1 9 40 193.214
99 2016-08-01 00:10:20 1 10 20 198.573
99 2016-08-01 00:12:00 1 12 0 194.432
99 2016-08-01 00:52:10 1 52 10 430.455
99 2016-08-01 00:55:50 1 55 50 400.739
99 2016-08-01 01:25:10 2 25 10 193.214
99 2016-08-01 01:25:50 2 25 50 193.032
99 2016-08-01 01:34:30 2 34 30 403.113
99 2016-08-01 01:37:10 2 37 10 417.18
99 2016-08-01 01:38:10 2 38 10 400.495
99 2016-08-01 03:57:00 4 57 0 190.413
99 2016-08-01 03:58:40 4 58 40 191.936

Here i have a value column, starting from the first record i need to find max value within next 60 seconds which will result in below. In the group of those 60 seconds, i need to select one record with max value.

id hist_timestamp DP mints Secnds value
99 2016-08-01 00:10:20 1 10 20 198.573
99 2016-08-01 00:12:00 1 12 0 194.432
99 2016-08-01 00:52:10 1 52 10 430.455
99 2016-08-01 00:55:50 1 55 50 400.739
99 2016-08-01 01:25:10 2 25 10 193.214
99 2016-08-01 01:34:30 2 34 30 403.113
99 2016-08-01 01:37:10 2 37 10 417.18
99 2016-08-01 03:57:00 4 57 0 190.413
99 2016-08-01 03:58:40 4 58 40 191.936

Can you please help me please with sql query.

Thanks !!!

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 10/23/2016 :  14:30:44  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I'm guessing by first recored you mean the row with the minumum hist_timestamp - I'm building this up with ctes
with cte1 as (select t = min(histtimestamp) from tbl)
so the period will be
, cte2 as (select t1=t, t2 = dateadd(ss,60,t) from cte1)
The rows in that period
, cte3 as (select t.* from tbl t join cte2 cte on t.hist_timestamp between cte.t1 and cte.t2)
Not sure about your result but it probably comes from
select *
from cte3
order by value

All untested


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 10/23/2016 14:31:43
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 10/23/2016 :  14:35:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
If you want to do it for all groups
with cte1 as (select id, t = min(histtimestamp) from tbl group by id)
, cte2 as (select id, t1=t, t2 = dateadd(ss,60,t) from cte1)
, cte3 as (select t.* from tbl t join cte2 cte on t.id = cte.id and t.hist_timestamp between cte.t1 and cte.t2)
select *
from cte3
order by id, value

hah - just checked the date - and I've found my way to the old form - doh!
Only came to find out what software the site used now as I'm thinking of resurrecting my forum - was surprised to see Snitz.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 10/23/2016 14:42:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000