How to get the first and the last record per group in SQL Server 2008?

Question

How can a select be crafted to where the first and last row of each set of the rows are grouped with a GROUP BY.

I've 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 I need to get is the first value of the column start and last value of the column end with group by the group column.

The resultant table should be as below:

   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 did a query but with FIRST_VALUE and LAST_VALUE and over (partition by). It works in SQL Server 2012 but didn't work in SQL Server 2008. I need a query that can be executed in SQL Server 2008.

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)