How to get the first and last record for each group in SQL?

Question

As the name suggests, I would like to select the first and last row of each set of rows grouped using GROUP BY

I have this table with the following data

 id group val start end 1 10 36 465 89 2 10 35 55 11 3 10 34 20 456 4 20 38 1140 1177 5 20 22 566 788 6 20 1235 789 4796 7 20 7894 741 1067 

what do I need to get the first value of the beginning of the column and the last value of the end of the column with the group by group column

The final table should be lower

  id group val start end 1 10 36 465 89 3 10 34 20 456 4 20 38 1140 1177 7 20 7894 741 1067 

I fulfilled the request, but with First_value and LAST_VALUE and over (partition by) it worked in sql server 2012 , but did not work in sql server 2008 I need a request that can be executed in sql server 2008

thanks

Answer 1

How about using ROW_NUMBER :

SQL Fiddle

 WITH Cte AS( SELECT *, RnAsc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val), RnDesc = ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY val DESC) FROM tbl ) SELECT id, [group], val, start, [end] FROM Cte WHERE RnAsc = 1 OR RnDesc = 1 ORDER BY [group], val 

Answer 2

This is one way -

 select t.* from tbl t join ( select [group], min(val) as val_1, max(val) as val_2 from tbl group by [group] ) v on t.[group] = v.[group] and (t.val = v.val_1 or t.val = v.val_2); 

Fiddle: http://sqlfiddle.com/#!3/c682f/1/0

Another approach:

 select id, [group], val, [start], [end] from( select t.*, max(val) over(partition by [group]) as max_grp, min(val) over(partition by [group]) as min_grp from tbl t ) x where val in (max_grp,min_grp) 

Answer 3

 select tt.id, tt.groups, tt.val, x.sstart, tt.[end] from test_table tt join (Select groups,First_value(start) over (partition by groups order by groups) sstart from test_table Union Select groups,Last_Value(start) over (partition by groups order by groups) sstart from test_table) as x on tt.start=x.sstart Order by tt.groups, sstart Desc