Without Primary Key Select / Delete First or Last n Records from Sql Server 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 when there is no primary key or unique identity column to filter the records. Or we can say getting and deleting specified number of records from top or bottom of the table without having any unique identity column in the table.

In previous article i explained Without primary key column update first or last n records in table and Select into statement to copy data from one table and insert into new table and Find all primary and foreign key constraints on each or any table in database and Difference between temporary table and table variable and Difference between primary key and foreign key in sql server

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 mention in my previous article SQL Query to Delete or Select First or Last n Records from Table. 

But what if there is no primary key column in the table? In such case we have to generate a unique value to each row of table using inbuilt Sql Function ROW_NUMBER() and then we can filter the records using Top and order by clause and perform select or delete operation on them as mention 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

(  

    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

BookName	Author	Publisher	BookPrice
Asp.Net	Ajay	Rozy Publication	1200.00
C#.Net	Sahil	Jai Publication	1000.00
VB.Net	Nancy	Rozy Publication	970.00
MVC	Sahil	Amar Publication	1480.00
JAVA	Supreet	Sam Publication	850.00
PHP	Parvesh	Maya Publication	800.00
ORACLE	Sunny	Amar Publication	1110.00
MYSQL	Shaheed	Sam Publication	400.00
jQuery	Amit	Maya Publication	950.00

--Select first five records

SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum ASC

RowNum	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 five records

SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum DESC

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

--Select first 2 records from table and delete

;WITH CTE AS

(

SELECT TOP 2 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum ASC

)

DELETE FROM CTE

--Check data after deletion

SELECT * FROM #tbBooks

BookName	Author	Publisher	BookPrice
VB.Net	Nancy	Rozy Publication	970.00
MVC	Sahil	Amar Publication	1480.00
JAVA	Supreet	Sam Publication	850.00
PHP	Parvesh	Maya Publication	800.00
ORACEL	Sunny	Amar Publication	1110.00
MYSQL	Shaheed	Sam Publication	400.00
jQuery	Amit	Maya Publication	950.00

--Select last 2 records from table and delete

;WITH CTE AS

(

SELECT TOP 2 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum DESC

)

DELETE FROM CTE

--Check data after deletion

SELECT * FROM #tbBooks

BookName	Author	Publisher	BookPrice
VB.Net	Nancy	Rozy Publication	970.00
MVC	Sahil	Amar Publication	1480.00
JAVA	Supreet	Sam Publication	850.00
PHP	Parvesh	Maya Publication	800.00
ORACEL	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