How to get last and first occurrence of items into separated columns?

I do have the fallowing table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1

I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9  
Ray     5         NULL
Charles 1         NULL

How can I write a query to get the last and first occurrence of values grouped by name and ordered by id, like this?

Test:

SELECT
  t_min_max.name,
  t_min.value AS value_1st,
  CASE WHEN min_id <> max_id THEN t_min.value END AS value_last
FROM (
  SELECT name, min(id) AS min_id, max(id) AS max_id
  FROM table 
  GROUP BY name
) AS t_min_max
JOIN table AS t_min
  ON t_min_max.min_id = t_min.id
JOIN table AS t_max
  ON t_min_max.max_id = t_max.id;

With complex queries, you should always work your way from the inside out,

SELECT name, min(id) AS min_id, max(id) AS max_id
FROM table 
GROUP BY name;

You can see here that we’re grabbing the range of IDs for each user,

id name     value
1  Daniel   3      <- this is a min(id)
2  Daniel   7
3  Daniel   2      <- this is a max(id)
4  Carol    9      <- this is a min(id)
5  Carol    4
6  Carol    9      <- this is a max(id)
7  Ray      5      <- this is both min(id) and max(id)
8  Charles  1      <- this is both min(id) and max(id)

After we do that we have a table like,

         min(id) | max(id)
Daniel | 1       | 3
Carol  | 4       | 6
Ray    | 7       | 7
Charles| 8       | 8

We then self-join that back to the original table in order to get the values.