Delete the last insert record with the same values
I have the following records inside a table:
User Product Quantity A Test 1 5 A Test 1 3 B Test 1 4 B Test 2 1
When I run 'DELETE FROM Sample WHERE User='A' AND Product='Test 1', I want to delete the last inserted record:
User Product Quantity A Test 1 5 B Test 1 4 B Test 2 1
Is this possible? Please advise.
Here's a working sql fiddle based on my comments.
Note that in the fiddle example I added an id and last_edited columns. I use GetDate() to fill the date time but add successive days( getdate() + 1..n ) to force unique date times...
The answer isn't the smoothest but demonstrates what I was talking about in the comments, and is correct. By the way, you didn't specify the version of SQL Server but if it's 2012 you should use the new, higher-precision date time type and also add a unique index on the column since your date times need to be unique identifiers in this scenario. Do you use the surrogate id though!
Repeating comments here:
What does "last" mean in your table? Without a date time column you cannot deterministically calculate which record was temporally placed last in the table. There is no internal metadata, in SQL Server at least, that would give you such info... I would also suggest using a surrogate primary key since none of your values are unique.