Selecting Distinct Rows but Counting All Rows
If you want the interestingData
and timestamp
from the same row (the most recent row that exceeds the threshold), and if you want to include all rows that exceed the threshold even if some rows for that ID don't meet the threshold, then:
;WITH x AS
(
SELECT ID, interestingData, [timestamp],
[count] = COUNT(1) OVER (PARTITION BY ID),
rn = ROW_NUMBER()
OVER (PARTITION BY ID ORDER BY [timestamp] DESC)
FROM dbo.tablename
WHERE interestingData > 300
)
SELECT ID, interestingData, [timestamp], [count]
FROM x
WHERE rn = 1;
Also, try to avoid data types and/or reserved keywords as column names. timestamp
is not a great choice because (a) it's not very meaningful and (b) it requires square brackets in a lot of scenarios.
Based on your sample, I believe you only need a GROUP BY ID with an HAVING clause on MIN([interestingData]) > 300.
Query:
SELECT ID
, interestingData = MIN([interestingData])
, timestamp = MIN([timestamp])
, count = COUNT([ID])
FROM Table1
GROUP BY ID
HAVING MIN([interestingData]) > 300;
SQL Fiddle with sample data and query.
Output:
ID | interestingData | timestamp | count
1 | 350 | 2016-01-23 17:01:00 | 3