Identifying results by group and rank using SQL
grouping, postgresql, sql, window-functions
Solution
The syntax should be as follows:
SELECT RANK() OVER(PARTITION BY a.id ORDER BY a.timestamp) AS rnk
Problem
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`.