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.

In previous articles i explained  Without Primary Key Select/Delete First or Last n Records from Table and Without primary key column update first or last n records in table and How to delete duplicate records from table and Difference between delete and truncate in sql server and Concatenate rows values as a comma separated string using for xml path and stuff and Copy only structure into a new table without data from existing 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.

• Tweet
• Share
• Share
• Share

Related Post