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

SELECT
  t_min_max.name,
  t_min.value AS value_1st,
  CASE WHEN min_id &lt;&gt; 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      &lt;- this is a min(id)
2  Daniel   7
3  Daniel   2      &lt;- this is a max(id)
4  Carol    9      &lt;- this is a min(id)
5  Carol    4
6  Carol    9      &lt;- this is a max(id)
7  Ray      5      &lt;- this is both min(id) and max(id)
8  Charles  1      &lt;- 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.