Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yasinirshad
Starting Member
18 Posts |
 Posted - 2016-10-19 : 02:04:23
|
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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2016-10-23 : 14:30:44
|
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. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2016-10-23 : 14:35:47
|
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. |
|
|
|
|
|
|
Sql query to find max value within 60 seconds ....