Deleting all entries, minus last 3

Question

Post reply

Deleting all entries, minus last 3

mlarson

SSC Veteran

Points: 294
More actions
August 21, 2008 at 12:43 pm

#196615

I have a database that logs all updated preferences, so the only real data I'm looking to keep are the last 3 entries from each customer. The amount of rows created for each customer varies based on how many updated entries they have made. Some might only have 3 entries, whereas other might have 120.
So I'm looking for some type of script/query that would search by customer_id, and time stamp, and remove all entries from said customer, except the last 3.
My SQL skills are still developing, so I'm sure this is an easy fix for somebody who has been around SQL for a decent amount of time.
So again this is basically what I'm wanting to do:
First - sort by customer_id , then time_stamp,
Then - delete all records from customer_id, except the last 3 entries (again based on timestamp)
Thank you for any help you may give!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 1

Hey there Larson,

When you say time stamp... is that column the datatype of timestamp or datetime?

Can you provide the definition of your table?

SELECT COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'YourTableName'

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Larson please post after reading the article whose link is in my signature block... It is meant to have you provide enough data so those here can best help you

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mlarson SSC Veteran Points: 294 More actions · Answer 3

Sorry I was posting that followup while the other post came through. I will go ahead and read that and do accordingly.

Thanks!

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 4

There are a couple of ways to skin the cat, but lately CTE's are in style.

Here I am creating a temp table for demonstration:

CREATE TABLE #TEST

(Customer_id INT,

last_name VARCHAR (25),

first_name VARCHAR (25),

email VARCHAR (25),

phone VARCHAR (25),

preference VARCHAR (25),

date_time DATETIME)

Populating it with some play data:

INSERT INTO #TEST

SELECT 1, 'SMITH', 'BOB','bsmith@bob.com', '911-911-9111', 'pref1', GETDATE()-1 UNION ALL

SELECT 1, 'SMITH', 'BOB','bsmith@bob.com', '911-911-9111', 'pref2', GETDATE() -2 UNION ALL

SELECT 1, 'SMITH', 'BOB','bsmith@bob.com', '911-911-9111', 'pref3', GETDATE() -3 UNION ALL

SELECT 1, 'SMITH', 'BOB','bsmith@bob.com', '911-911-9111', 'pref4', GETDATE()-4 UNION ALL

SELECT 1, 'SMITH', 'BOB','bsmith@bob.com', '911-911-9111', 'pref5', GETDATE() -5 UNION ALL

SELECT 1, 'SMITH', 'BOB','bsmith@bob.com', '911-911-9111', 'pref16', GETDATE()-6 UNION ALL

SELECT 2, 'JOHNSON', 'JIM','jjohnson@jon.com', '911-911-9111', 'pref1', GETDATE() -7UNION ALL

SELECT 2, 'JOHNSON', 'JIM','jjohnson@jon.com', '911-911-9111', 'pref2', GETDATE() -8UNION ALL

SELECT 2, 'JOHNSON', 'JIM','jjohnson@jon.com', '911-911-9111', 'pref3', GETDATE()-9 UNION ALL

SELECT 2, 'JOHNSON', 'JIM','jjohnson@jon.com', '911-911-9111', 'pref4', GETDATE()-10 UNION ALL

SELECT 2, 'JOHNSON', 'JIM','jjohnson@jon.com', '911-911-9111', 'pref5', GETDATE()-11 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE() -12 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE()-13 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE()-14 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE() -15 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE()-16 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE() -17 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE() -18 UNION ALL

SELECT 3, 'JONES', 'JILL','jjones@jim.com', '911-911-9111', 'pref1', GETDATE()

Creating a CTE using ROW_NUMBER function ordered by date_time descending, and joining that to the DELETE statement:

WITH CUSTOMERS AS

(SELECT

CUSTOMER_ID,

DATE_TIME,

ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY DATE_TIME DESC) AS RECORD#

FROM #TEST )

DELETE #TEST

FROM #TEST T

JOIN CUSTOMERS C ON T.CUSTOMER_ID = C.CUSTOMER_ID

AND C.DATE_TIME = T.DATE_TIME

WHERE C.RECORD# > 3

SELECT * FROM #TEST

Clean up:

DROP TABLE #TEST

Does that help out?

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

mlarson SSC Veteran Points: 294 More actions · Answer 5

OK, so here you go..

Create table request_list

(customer_id number (6),

first_name varchar (15),

last_name varchar (20),

email_address varchar (30),

phone_number number (9),

preference_entry varchar (50),

Date datetime);

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 6

Same type of logic here, but almost impossible to wrap one's head around the derived table and correlated subquery.

To this day, I still have to deal with code that looks like this...

ICK! The CTE is a beautiful thing for readability!

DELETE #TEST

FROM #TEST T1

JOIN (SELECT CUSTOMER_ID, DATE_TIME

FROM #TEST T3

WHERE DATE_TIME < (SELECT TOP 1 DATE_TIME

FROM (SELECT TOP 3 DATE_TIME

FROM #TEST T4

WHERE T4.CUSTOMER_ID = T3.CUSTOMER_ID

ORDER BY DATE_TIME DESC) T5

ORDER BY DATE_TIME ASC)) T2

ON T1.CUSTOMER_ID = T2.CUSTOMER_ID

AND T1.DATE_TIME = T2.DATE_TIME

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

mlarson SSC Veteran Points: 294 More actions · Answer 7

Thanks Todd! That is exactly what I'm looking for!