SQL Query to Delete or Select First or Last n Records From Table

Introduction: In this article I am going to explain how to select or delete first or last 5 or 10 or any number of rows from table in SQL Server. Or we can say getting and deleting specified number of records from top or bottom of the table.


Description: To delete specified number of records from table we need a unique column for example primary key column through which we can filter the first or last n number of records using TOP and ORDER BY clause as I have used in the queries below.


Implementation: Let’s create a table and delete n number of records from first or last.

--Create a table using following script
 CREATE TABLE tbBooks
(
    BookId     INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    BookName   VARCHAR(100),
    Author     VARCHAR(100),
    Publisher  VARCHAR(100),
    BookPrice  DECIMAL(10,2)
)

--Add some dummy data into the table
GO
INSERT INTO tbBooks VALUES
('Asp.Net','Ajay','Rozy Publication',1200),
('C#.Net','Sahil','Jai Publication',1000),
('VB.Net','Nancy','Rozy Publication',970),
('MVC','Sahil','Amar Publication',1480),
('JAVA','Supreet','Sam Publication',850),
('PHP','Parvesh','Maya Publication',800),
('ORACLE','Sunny','Amar Publication',1110),
('MYSQL','Shaheed','Sam Publication',400),
('jQuery','Amit','Maya Publication',950)


 --Check inserted data
SELECT * FROM tbBooks

BookId
BookName
Author
Publisher
BookPrice
1
Asp.Net
Ajay
Rozy Publication
1200.00
2
C#.Net
Sahil
Jai Publication
1000.00
3
VB.Net
Nancy
Rozy Publication
970.00
4
MVC
Sahil
Amar Publication
1480.00
5
JAVA
Supreet
Sam Publication
850.00
6
PHP
Parvesh
Maya Publication
800.00
7
ORACLE
Sunny
Amar Publication
1110.00
8
MYSQL
Shaheed
Sam Publication
400.00
9
jQuery
Amit
Maya Publication
950.00


--Select first 5 records
SELECT TOP 5 * FROM tbBooks ORDER BY BookId ASC

BookId
BookName
Author
Publisher
BookPrice
1
Asp.Net
Ajay
Rozy Publication
1200.00
2
C#.Net
Sahil
Jai Publication
1000.00
3
VB.Net
Nancy
Rozy Publication
970.00
4
MVC
Sahil
Amar Publication
1480.00
5
JAVA
Supreet
Sam Publication
850.00


--Select last 5 records
SELECT TOP 5 * FROM tbBooks ORDER BY BookId DESC

BookId
BookName
Author
Publisher
BookPrice
9
jQuery
Amit
Maya Publication
950.00
8
MYSQL
Shaheed
Sam Publication
400.00
7
ORACLE
Sunny
Amar Publication
1110.00
6
PHP
Parvesh
Maya Publication
800.00
5
JAVA
Supreet
Sam Publication
850.00


--Delete first 2 records
DELETE FROM tbBooks
WHERE BookId IN (SELECT TOP 2 BookId FROM tbBooks ORDER BY BookId ASC)

--Check data in table
SELECT * FROM tbBooks 

BookId
BookName
Author
Publisher
BookPrice
3
VB.Net
Nancy
Rozy Publication
970.00
4
MVC
Sahil
Amar Publication
1480.00
5
JAVA
Supreet
Sam Publication
850.00
6
PHP
Parvesh
Maya Publication
800.00
7
ORACLE
Sunny
Amar Publication
1110.00
8
MYSQL
Shaheed
Sam Publication
400.00
9
jQuery
Amit
Maya Publication
950.00


--Delete last 2 records
DELETE FROM tbBooks
WHERE BookId IN (SELECT TOP 2 BookId FROM tbBooks ORDER BY BookId DESC)

--Check data in table
SELECT * FROM tbBooks 

BookId
BookName
Author
Publisher
BookPrice
3
VB.Net
Nancy
Rozy Publication
970.00
4
MVC
Sahil
Amar Publication
1480.00
5
JAVA
Supreet
Sam Publication
850.00
6
PHP
Parvesh
Maya Publication
800.00
7
ORACLE
Sunny
Amar Publication
1110.00

 Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..