2

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?

3
  • 1
    What criteria do you use to determine first-last? Do not say it is "physical posession" - this concept do not exists in database until some ORDER BY applied.
    – Akina
    Aug 14, 2018 at 17:48
  • sorry, I forgot to mention the id column, I will improve the question Aug 14, 2018 at 17:51
  • Follow the tag I added.
    – Rick James
    Aug 26, 2018 at 4:35

1 Answer 1

6

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.

5
  • 1
    This would be an even better answer if you gave a short explanation of how it works.
    – RDFozz
    Aug 14, 2018 at 18:51
  • @RDFozz Sorry, but I don't understand what can be incomprehensible and, accordingly, what needs to be explained.
    – Akina
    Aug 15, 2018 at 4:25
  • @RDFozz try that. Aug 26, 2018 at 5:16
  • @Akina like that =) Aug 26, 2018 at 5:16
  • @EvanCarroll - Yes, like that!
    – RDFozz
    Aug 26, 2018 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.