0

Similar to this question but with the possibility that the same name can appear again in the following rows.

I have this 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
9  Daniel   6
10 Daniel   5
11 Daniel   5

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         5
Charles 1         1
Daniel  6         5
0
1
WITH 
cte1 AS ( SELECT *, 
                 CASE WHEN name = LAG(name) OVER (ORDER BY id) 
                      THEN 0 
                      ELSE 1 
                      END the_same
          FROM test ),
cte2 AS ( SELECT *, SUM(the_same) OVER (ORDER BY id) group_num
          FROM cte1 )
SELECT DISTINCT
       name, 
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id) value_1st,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id DESC) value_last 
FROM cte2;

fiddle

-1

SELECT s.name,(CASE WHEN s.name =t.name THEN MAX(s.id) END) AS 'value_1st',(CASE WHEN s.name=t.name AND s.id =MAX(s.id) THEN s.value END) AS 'value_last' FROM tableA s,(SELECT distinct name FROM tableA)t WHERE s.name = t.name

1
  • Syntax error guaranteed. Not full grouping. – Akina Feb 14 '20 at 8:55

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.