Binaryworld - How to find first/last record in the group? ... [ SQL Server -> T-SQL ]

Yes this is certainly a common situation where you have to write a query to only extract first or last record from the group.

For example you might want to extract only first product from each category. Let's look at some examples.

Click here to copy the following block

Drop table #TempTable

Go
create table #TempTable
(
Bookid int NOT NULL,
BLevel varchar (10) NOT NULL,
Book varchar (25) NOT NULL,
BookRating int NOT NULL
)

GO

insert into #TempTable values (1, 'Beginner', 'Learn VB')
insert into #TempTable values (2, 'Beginner', 'Learn MS Word')
insert into #TempTable values (3, 'Beginner', 'Learn VC++')
insert into #TempTable values (4, 'Beginner', 'Learn MS Word')
insert into #TempTable values (5, 'Advanced', 'Learn ORACLE')
insert into #TempTable values (6, 'Advanced', 'Learn DB2')
insert into #TempTable values (7, 'Advanced', 'Learn SQL')

GO

--//Show all records
Select * from #TempTable

Bookid      BLevel     Book                      
----------- ---------- ------------------------- 
1           Beginner   Learn VB
2           Beginner   Learn MS Word
3           Beginner   Learn VC++
4           Beginner   Learn MS Word
5           Advanced   Learn ORACLE
6           Advanced   Learn DB2
7           Advanced   Learn SQL

Click here to copy the following block

--//Find first book from each level
select t1.* from #TempTable as t1
where t1.Bookid = (select min(t2.BookId) from #TempTable as t2
where t2.BLevel = t1.BLevel)

Bookid      BLevel     Book                      
----------- ---------- ------------------------- 
5           Advanced   Learn ORACLE
1           Beginner   Learn VB

Click here to copy the following block

--//Find last book from each level
select t1.* from #TempTable as t1
where t1.Bookid = (select max(t2.BookId) from #TempTable as t2
where t2.BLevel = t1.BLevel)

Bookid      BLevel     Book                      
----------- ---------- ------------------------- 
4           Beginner   Learn MS Word
7           Advanced   Learn SQL

Click here to copy the following block

--//Find first and last books from each level
select t1.* from #TempTable as t1
where t1.Bookid = (select min(t2.BookId) from #TempTable as t2
         where t2.BLevel = t1.BLevel)
OR   t1.Bookid = (select max(t2.BookId) from #TempTable as t2
         where t2.BLevel = t1.BLevel)

Bookid      BLevel     Book                      
----------- ---------- ------------------------- 
1           Beginner   Learn VB
4           Beginner   Learn MS Word
5           Advanced   Learn ORACLE
7           Advanced   Learn SQL