CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2460074
user2460074

Reputation: 1272

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

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.

Upvotes: 14

Views: 16785

Answers (5)

Thomas Riedel
Thomas Riedel

Reputation: 71

Here is a bankaccount example:

create table transactions (
id integer identity(1,1),
aDateTime datetime not null default getdate(),
OnHand float,
AmountWithDrawn float,
AmountDeposited float,
NewOnhand float
)

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('01-jan-2021 08:15', 0, 0, 1000, 1000);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('05-jan-2021 08:15', 1000, 100, 0, 900);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('06-jan-2021 08:15', 900, 200, 0, 700);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('01-feb-2021 08:15', 700, 0, 1000, 1700);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('09-feb-2021 08:15', 1700, 200, 0, 1500);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('10-feb-2021 08:15', 1500, 300, 0, 1200);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('01-mar-2021 08:15', 1200, 0, 1000, 2200);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('07-mar-2021 08:15', 2200, 400, 0, 1800);

insert into transactions (aDateTime, OnHand, AmountWithDrawn, AmountDeposited, NewOnhand) values ('11-mar-2021 08:15', 1800, 500, 0, 1300);

List:

select * from transactions order by id

enter image description here

Now we want to group per month:

select distinct month(adatetime) aMonth, sum(amountWithDrawn) SumWithDrawn, sum(amountDeposited) SumDeposited from transactions group by month(adatetime);

enter image description here

But how can we get Onhand and NewOnhand in the query?

with cte as
(select distinct month(adatetime) aMonth, sum(amountWithDrawn) SumWithDrawn, sum(amountDeposited) SumDeposited, min(id) MinId, max(id) MaxId from transactions group by month(adatetime)) 
select aMonth, (select Onhand from transactions where id = minid) FirstOnhand, SumWithDrawn, SumDeposited, (select NewOnhand from transactions where id = maxid) LastNewOnhand from cte

enter image description here

This way would be quite efficient (add index to ID) and the code quite is easy to read and comprehend.

Upvotes: 0

TaTa
TaTa

Reputation: 19

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

Upvotes: 0

Gonzalo
Gonzalo

Reputation: 1

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

Upvotes: -1

Brian DeMilia
Brian DeMilia

Reputation: 13248

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)

Upvotes: 6

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 16

Related Questions