Title: Time difference between two rows for specified ID
Tags: sql-server-2012
Question: I'm trying to find the time difference in seconds between two rows that have the same ID.
Here's a simple table.
The table is ordered by myid and timestamp. I'm trying to get the total second between two rows that have the same myid.
Here's what I have come up with. The only problem with this query is that it calculates the time difference for all records but not for the same ID.
SELECT  DATEDIFF(second, pTimeStamp, TimeStamp), q.*
FROM    (
SELECT  *,
LAG(TimeStamp) OVER (ORDER BY TimeStamp) pTimeStamp
FROM    data
) q
WHERE   pTimeStamp IS NOT NULL
This is the output.
I only want the output highlighted in yellow.
Any suggestions?
SQLFIDDLE
Comment: @shawnt00 Thank you! That what I needed. If you write your comment as answer, I'll accepted as correct answer.

Comment: Add `partition by ID`

Comment: "Tables" are not ordered by anything, queries and result-sets are.  Nowhere in your query are you either ordering nor partitioning by ID, so the results are not ordered by ID either.

Here is the accepted answer: The fix is simply a matter of narrowing the window, with PARTITION BY, to rows with the same ID:
SELECT  DATEDIFF(second, pTimeStamp, TimeStamp), q.*
FROM    (
SELECT  *,
LAG(TimeStamp) OVER (PARTITION BY ID ORDER BY TimeStamp) pTimeStamp
FROM    data
) q
WHERE   pTimeStamp IS NOT NULL


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:01/05/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql-server-2012>', 'Title': 'Time difference between two rows for specified ID', 'ViewCount': 1503, 'id': 34620372, 'user_AboutMe': '<p>Software developer.</p>\n', 'user_AccountId': 1528778.0, 'user_DisplayName': 'smr5', 'user_Reputation': 2493.0, 'user_Views': 296.0, 'user_id': 1426542.0}}