Delete a record and all the record that precede it

Question

I have a table that has a primary key based on GUIDs. I need to find a record in this table based on the primary key and then delete this record and all the record that appear before it. Is there a command to delete preceding records or can anyone give me some example SQL on how I could do this.

I am using SLQ server 2008

Answer 1

In your question you state "that appear before it." or "preceding records" This concept needs to be defined more explicitly. SQL is based on Set theory - and in a set there is no implicit order to the items in the set (rows in the table) You have to define what preceding means based on the value of some attribute of the row...

Answer 2

Since you are using a GUID as the primary key there cannot be any order determined from it alone (unless you used NEWSEQUENTIALID(), and even that would only be sequential on the same box until restarted), so you need another column, such as a timestamp or Identity to order your rows.

Answer 3

Given the nature of a GUID and the fact that you are using it as a Primary Key, then NO, you can not accomplish this.

If you have a secondary field that might help with the ordering then Yes, or if you had an IDENTITY field.

• Uniqueidentifier vs. IDENTITY
• The Cost of GUIDs as Primary Keys

Based on the DateColumn specified:

Have a look at this

DECLARE @Table TABLE(
     ID uniqueidentifier,
     DateVal DateTime
)

INSERT INTO @Table SELECT NEWID(), '06 Jan 2009'
INSERT INTO @Table SELECT NEWID(), '05 Jan 2009'
DECLARE @GUID uniqueidentifier
SELECT @GUID = NEWID()
INSERT INTO @Table SELECT @GUID, '04 Jan 2009'
INSERT INTO @Table SELECT NEWID(), '03 Jan 2009'
INSERT INTO @Table SELECT NEWID(), '02 Jan 2009'
INSERT INTO @Table SELECT NEWID(), '01 Jan 2009'

SELECT  *
FROM    @Table

DELETE FROM @Table
WHERE   DateVal <= (SELECT DateVal FROM @Table WHERE ID = @GUID)

SELECT  *
FROM    @Table

Answer 4

It's possible to extract the date/time from some GUIDs, depending on how they were created, however it's generally a bad idea. As others have mentioned you should be using an identity or a timestamp -- it's easier, safer and saner.

Relevant links:

• An extraction utility: http://www.famkruithof.net/uuid/uuidgen?typeReq=-1
• Some more technical reading material: http://www.ietf.org/rfc/rfc4122.txt