Question: I have a table containing `positions`
This table is as follows:

```
user_id | current | started_at  | finished_at
2       |  false  | 10-07-2016  | 02-08-2016
1       |  false  | 19-07-2016  | 27-07-2016
1       |  true   | 29-07-2016  | null
3       |  true   | 20-07-2016  | null
3       |  false  | 01-07-2016  | 18-07-2016

```

I'm sorting this table using a case to use either the `started_at` or `finished_at` date depending on whether `current` is true or false

```
SELECT *
FROM positions
ORDER BY
  CASE
      WHEN current = true 
          THEN started_at
      ELSE finished_at
  END
 DESC

```

This works fine as expect but now I want to extract only the first row for each `user_id`

So in my example data i'd like to only have the following returned.

```
user_id | current | started_at  | finished_at
2       |  false  | 10-07-2016  | 02-08-2016
1       |  true   | 29-07-2016  | null
3       |  true   | 20-07-2016  | null

```

I was thinking this could be done with a `GROUP BY` but I can't get it to work without error or maybe I need a sub query, I'm not sure.

Answer: 
This is an excellent opportunity to use a window function. You can use the window function to create a query that looks like this:

```
SELECT  user_id, current, started_at, finished_at, 
  row_number() OVER (PARTITION BY user_id) AS row_number
    FROM positions
    ORDER BY CASE WHEN current = true 
       THEN started_at ELSE finished_at END DESC

```

This will give you your original table with a new column "row\_number" that comes from the window function. You partition by user\_id because you want to get the row\_number by user. Use the ORDER clause you provided. To get the full answer just use this statement as a subquery, use the WHERE clause to only choose row\_number = 1, and pull all the fields you need. Window functions cannot be used in the WHERE clause, which is why you need the subquery.

```
SELECT  user_id, current, started_at, finished_at
FROM
(
SELECT  user_id, current, started_at, finished_at, 
  row_number() OVER (PARTITION BY user_id) AS row_number
FROM positions
ORDER BY CASE WHEN current = true 
   THEN started_at ELSE finished_at END DESC
) pos
WHERE row_number= 1

```
I guess you made a mistake in your example, since your desired result shows the *second* row for `user_id` 1.

You can easily achieve what you want with the non-standard PostgreSQL `DISTINCT ON` feature:

```
SELECT DISTINCT ON (user_id) * FROM
   (SELECT * FROM positions
    ORDER BY
       CASE
          WHEN current = true
          THEN started_at
          ELSE finished_at
       END
    DESC) q;

```

This will remove all but the first row for each `user_id`.