Re: SQL Help

#1

On Fri, 05 Dec 2003 04:05:58 +0000, How'd they do that? wrote:

Quote:

I've got a table with the following layout:

create table CUST_HIST (
customer_id int,
activity_date datetime
col_a int,
col_b int,
col_c int)

I want to delete old data from the table and leave the most recent twenty
rows (based on date) for each customer. Is there an efficient way to do
this (ie: not using cursors)?

We're using ASE 12.5.0.3

Thanks.
Howabout:

set rowcount 20
select * into #temp from CUST_HIST
delete from CUST_HIST
insert into CUST_HIST select * from #temp

#2

On Fri, 05 Dec 2003 19:21:28 +0000, Glen K wrote:

Quote:

On Fri, 05 Dec 2003 04:05:58 +0000, How'd they do that? wrote:

[quoted text muted]
Howabout:

set rowcount 20
select * into #temp from CUST_HIST
delete from CUST_HIST
insert into CUST_HIST select * from #temp

oops... forgot the 'order by'

set rowcount 20
select * into #temp from CUST_HIST order by activity_date
delete from CUST_HIST
insert into CUST_HIST select * from #temp

#3

How'd they do that? wrote:

Quote:

I've got a table with the following layout:

create table CUST_HIST (
customer_id int,
activity_date datetime
col_a int,
col_b int,
col_c int)

I want to delete old data from the table and leave the most recent twenty
rows (based on date) for each customer. Is there an efficient way to do
this (ie: not using cursors)?

Here's the basic idea off the top of my head. You'll have to tune
this to soemthing that acutally works.

delete from cust_hist
where customer_id not in (
select customer_id
from cust_hist o
where 20 < (
select count(*)
from cust_hist i
where i.activity_date > o.activity_date
) )

IE, select the cust_hist rows where fewer than 20 rows have a later
date. These are the latest 20. Delete the rows that are not one
of these 20.

Larry Coon
University of California

#4

Another solution :

-- Step 1 : Keep 20 more recent rows in a temporary table
SET ROWCOUNT 20
SELECT * INTO #keep_them FROM CUST_HIST ORDER BY activity_date DESC
SET ROWCOUNT 0

-- Step 2 : Remove all rows
DELETE FROM CUST_HIST

-- Step 3 : Restore 20 more recent rows
INSERT INTO CUST_HIST SELECT * FROM #keep_them

"Larry Coon" <lcnospam (AT) assist (DOT) org> a écrit dans le message de news:
3FD0DF0B.490E (AT) assist (DOT) org...

Quote:

How'd they do that? wrote:

I've got a table with the following layout:

create table CUST_HIST (
customer_id int,
activity_date datetime
col_a int,
col_b int,
col_c int)

I want to delete old data from the table and leave the most recent
twenty
rows (based on date) for each customer. Is there an efficient way to do
this (ie: not using cursors)?

Here's the basic idea off the top of my head. You'll have to tune
this to soemthing that acutally works.

delete from cust_hist
where customer_id not in (
select customer_id
from cust_hist o
where 20 < (
select count(*)
from cust_hist i
where i.activity_date > o.activity_date
) )

IE, select the cust_hist rows where fewer than 20 rows have a later
date. These are the latest 20. Delete the rows that are not one
of these 20.

Larry Coon
University of California

#5

perhaps something like this could help:

select * , newcol = identity (10)
into #mytemp from CUST_HIST order by customer_id ASC, activity_date DESC

select CutPoint = min(newcol) + 20 , customer_id
into #mytemp2
from #mytemp
group by customer_id

delete #mytemp
from #mytemp t1, #mytemp2 t2
where t1.customer_id = t2.customer_id
and t1.newcol > t2.CutPoint

delete CUST_HIST

insert CUST_HIST
select customer_id , activity_date , col_a, col_b , col_c from #mytemp
"How'd they do that?" <rmcgorman_usenet (AT) spamex (DOT) com> wrote

Quote:

I've got a table with the following layout:

create table CUST_HIST (
customer_id int,
activity_date datetime
col_a int,
col_b int,
col_c int)

I want to delete old data from the table and leave the most recent twenty
rows (based on date) for each customer. Is there an efficient way to do
this (ie: not using cursors)?

We're using ASE 12.5.0.3

Thanks.

#6

How'd they do that? wrote:

Quote:

Only keeps last 20 rows, not last 20 per customer. Right?

Oops, my suggestion didn't. I missed reading that part.
Change it to something like this:

delete from cust_hist
where customer_id in (
select customer_id
from cust_hist o
where 20 >= (
select count(*)
from cust_hist i
where i.activity_date > o.activity_date
and i.customer_id = o.customer_id
) )

Again, untested and unverified, so take it for what
it's worth.

Larry Coon
University of California

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: SQL Help

comp.databases.sybase comp.databases.sybase

Re: SQL Help - 12-05-2003 , 01:21 PM

Re: SQL Help - 12-05-2003 , 01:23 PM

Re: SQL Help - 12-05-2003 , 01:39 PM

Re: SQL Help - 12-05-2003 , 05:14 PM

Re: SQL Help - 12-08-2003 , 09:36 AM

Re: SQL Help - 12-08-2003 , 11:53 AM