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 '18 at 17:48
  • sorry, I forgot to mention the id column, I will improve the question Aug 14 '18 at 17:51
  • Follow the tag I added.
    – Rick James
    Aug 26 '18 at 4:35
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 '18 at 18:51
  • @RDFozz Sorry, but I don't understand what can be incomprehensible and, accordingly, what needs to be explained.
    – Akina
    Aug 15 '18 at 4:25
  • @RDFozz try that. Aug 26 '18 at 5:16
  • @Akina like that =) Aug 26 '18 at 5:16
  • @EvanCarroll - Yes, like that!
    – RDFozz
    Aug 26 '18 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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