How to get the first and the last record per group

问题:

As the title suggests, I'd like to select the first and last row of each set of rows 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.

回答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

回答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)

回答3:

How to two query 'UNION'

SELECT TOP 1 EmployeeId, AttendenceId, Intime
FROM EmployeeAttendence
WHERE AttendenceDate >='1/18/2020 00:00:00' 
  AND AttendenceDate <='1/18/2020 23:59:59'
GROUP BY EmployeeId,AttendenceId,Intime
ORDER BY AttendenceId  

SELECT TOP 1 EmployeeId, AttendenceId, OutTime
FROM EmployeeAttendence
WHERE AttendenceDate >='1/18/2020 00:00:00' 
  AND AttendenceDate <='1/18/2020 23:59:59'
GROUP BY EmployeeId, AttendenceId, OutTime
ORDER BY AttendenceId desc 

回答4:

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