Without primary key how to delete last n records from table in SQL

Question

1.00/5 (1 vote)

See more:

How to delete last n records from table in sql when there is no primary key column in table.Please help me in this query

What I have tried:

I know how to delete last n records with primary key but without primary key i was not able to delete.

Posted 30-Mar-16 21:03pm

xpertzgurtej

Updated 30-Mar-16 22:47pm

Add a Solution

Comments

phil.o 31-Mar-16 3:12am

Please describe your table and what you actually tried.

Patrice T 31-Mar-16 4:36am

The OP gave you an answer below.

phil.o 31-Mar-16 4:47am

Thanks for the notification, I would not have seen it otherwise.

Patrice T 31-Mar-16 4:48am

you welcome.

xpertzgurtej 31-Mar-16 3:20am

Suppose the table has Only 3 fields . FirstName, MiddleName and LastName. And there are 1000 records and i want to delete last 10 records. Then how to delete them

Andy Lanng 31-Mar-16 3:28am

You have to "reply" to comments otherwise we won't get a notification.
Without a pk or time stamp there is no way to identify the last n records added to a table.

Tomas Takac 31-Mar-16 4:11am

Define "last". Terms like "first" or "last" only have meaning within a sorted set. So what's your sort order?

jaket-cp 31-Mar-16 4:44am

Depends on what you mean by last 10 records.
If you mean the last 10 records that have been inserted into the table, then you will probably have to look in the transaction logs.

But if you want to delete the last 10 records ordered by FirstName, MiddleName or LastName, then that is more straight forward.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2016-03-30T22:47:00

Quote:
Suppose the table has Only 3 fields . FirstName, MiddleName and LastName. And there are 1000 records and i want to delete last 10 records. Then how to delete them

Then you will have to get a determinant for your "last 10 records" statement.
If you do not have any field than can discriminate the last record (from a sequence number, or a datetime for example), then you have to find some sort of criteria. From the informations you gave us, the only criteria I can imagine is an alphabetical one.
If you really need to delete the "last 10 inserted records", then you should include a column in your table that will serve as a discriminant.
If you do not have such a column, then you will only be able to sort on text fields. And if you do not sort the records before deleting the first 10 of them, then you will delete 10 random records.
Important thing here is you should have a field in your table that will be able to discriminate the "last 10 records".
Hope this helps.