I have a table with the following structure:
id timestamp area
717416915 18:30:53.063 25.691601
717416915 18:31:34.863 31.200506
717416915 18:32:23.665 25.690088
1994018321 18:32:45.467 37.409171
1994018321 18:33:19.612 37.409171
424164505 18:36:16.634 18.22091
424164505 18:36:36.899 18.210754
424164505 18:37:08.614 19.829266
2394018356 18:37:27.231 79.31705
What I want to do is to summarize the values in such a way that I can identify the area per id
, ordered by timestamp
. For example if I wanted the first area
value it would be the following:
id timestamp area_1
717416915 18:30:53.063 25.691601
1994018321 18:32:45.467 37.409171
424164505 18:36:16.634 18.22091
2394018356 18:37:27.231 79.31705
And if I wanted to get the second area
value per id
it would be the following:
id timestamp area_2
717416915 18:31:34.863 31.200506
1994018321 18:33:19.612 37.409171
424164505 18:36:36.899 18.210754
I understand that I need to sort by time, and then identify the first value per id
. I don't quite understand how to do this. What I have tried doing is the following (which is not running, as I am still a little unclear on how to use the OVER
function).
WITH T AS (
SELECT * OVER(PARTITION BY a.id ORDER BY a.timestamp) AS rnk
FROM mytable AS a
)
SELECT area as area_1
FROM T
WHERE rnk = 1
GROUP BY a.id
ORDER BY a.timestamp;
I was planning to use rnk=2
etc, to get subsequent area values per id
.
The syntax should be as follows:
SELECT RANK() OVER(PARTITION BY a.id ORDER BY a.timestamp) AS rnk