6

I have the following table:

----------------------------------------------
| ID | interestingData |      timestamp      |
----------------------------------------------
|  1 |       400       | 2016-01-23 17:01:00 |
----------------------------------------------
|  1 |       400       | 2016-01-24 17:01:00 |
----------------------------------------------
|  1 |       350       | 2016-01-25 17:01:00 |
----------------------------------------------
|  2 |       23        | 2016-01-23 17:01:00 | 
----------------------------------------------
|  2 |       34        | 2016-01-24 17:01:00 | 
----------------------------------------------
|  2 |       12        | 2016-01-25 17:01:00 | 
----------------------------------------------

Where our PK is (ID, timestamp). I'm attempting to determine a query that will give me the unique IDs and the latest interestingData for which interestingData exceeds a threshold. That would, of course, be done with:

SELECT DISTINCT ID
FROM table
WHERE interestingData > threshold
ORDER BY timestamp DESC;

However, I want the count of every occurrence where interestingData exceeded the threshold. My results table would ideally look like

------------------------------------------------------
| ID | interestingData | timestamp           | count |    
------------------------------------------------------
|  1 |        350      | 2016-01-25 17:01:00 |   3   |
------------------------------------------------------

Were my threshold 300. I am aware that if you want to pair something distinct with a set of data then a left outer join is going to be in order, but I'm not entirely sure how to go about it. This is the closest I can think of so far.

SELECT DISTINCT ID
FROM table t1
LEFT OUTER JOIN table t2 ON t1.ID = t2.table.ID
WHERE interestingData > 300
ORDER BY timestamp DESC

This gets me the distinct IDs and pairs them with the rest of the data as I need, but no provisions for getting the other parts of the results, let alone the count.

  • 3
    So interestingData and timestamp should come from the same (latest) row, yes? Not just be random aggregates like MIN or MAX? Also, if there is a 4th row for ID = 1 but now interestingData = 50, and a 4th row for ID = 2 but now interestingData = 500, what should the result be? Think about all the edge cases you might encounter after being provided a solution before asking for a solution (if you wait until after, it just means more chances that the solution has to change). – Aaron Bertrand Feb 2 '16 at 1:42
7

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.

2

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
  • Except if you aggregate on both interestingData and timestamp, you can get values for those columns from different rows, as is the case here. This doesn't match the OP's desired/ideal output. Also, if you add a 4th row for ID = 1 where the threshold isn't met, then even the rows where the threshold is met are ignored and the query returns 0 results. – Aaron Bertrand Feb 2 '16 at 1:39

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.