T-SQL Challenges - Fetching the first and last record from the table

Problem statement:
Considering am having lot of records(Bank transaction) in a table.
1. I want the account number which repeats more than one times.
2. After filtering the records in the above criteria, I need only the first transaction and the last transaction.
How can we achieve it? Lets see,
-- Am creating the table
create table venkat(id int, id1 int,nam varchar(10))
insert into venkat values(1,1,'Venkat')
insert into venkat values(1,2,'Venkat')
insert into venkat values(1,3,'Venkat')
insert into venkat values(1,4,'Venkat')
insert into venkat values(1,5,'Venkat')
insert into venkat values(2,5,'Venkat')
select * from venkat

This kind of problem can be resolved using Common Table expression (a new feature in SQL Server 2005). CTE enable us to divide a huge query into smaller chunks and we can easily write a complex logic in simple terms.

with cte as
(
select * from
(
select count(id) as cnt,id from venkat group by id

-- Getting the records with more than 1 record
)t where cnt >1
),
cte1 as
(
select row_number()over(partition by id order by id1 ) as rownumber ,id,id1,nam from venkat
where id in (select id from cte)

-- Partitioning the records using rownumber partition concept
)
,
cte2 as
(
select * from cte1 where (rownumber=1 OR rownumber = (select max(rownumber) from cte1))

-- Fetching only 1 and last record in each individual clients
)
select * from cte2

Regards,
Venkatesan Prabu .J