Deleting all but most recent rows

[grega]

Hi all ... could be simple but I'm banging my head against a wall here. I have a table with 2 columns & many rows. It looks like ;

org_no date
-----------------------
111 01-01-1999
111 12-08-1998
111 03-03-1998
222 13-05-2000
222 22-09-1999
..
..
..
etc....

No limit on number of rows per organisation.

What I want to do is delete all but the most recent 6 rows for each organisation. I've been playing around with order by/group by/having, etc. I also know it can be done quite simply with cursors, but is there another way with standard SQL?

Any help/advice is much appreciated. (I'm using Sybase but any pseudo-SQL will do).

Greg. [sig][/sig]

 

[listener22]

There may be better ways, but one way is to build a temp
table made up of the six most recent dates for any given org.
Then you can either drop the first table and rename the
temp table, or you can delete records from the first table
that are not found in the temp table.
There is at least one other thread floating around that
discussed how to select the top or bottom x number of
ordered records. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[bigscouse]

You could set up an SQL script to count the org_no's and delete the earliest record where the count is greater than 6.
You would have to run it repeatedly though, maybe in a shell script.
Might not be the best answer but if I think of something else I will let you know. [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]

 

[MalcolmW]

This will pick the top 6 in each org_no, or as many as there are if there are fewer than 6. This could be incorporated into a delete statement as part of the WHERE NOT EXISTS...

I would like to give credit to Joe Celko's SQL For Smarties for this, but the similar example in the book had several errors that required correction, plus I adapted it to pick the top from groups, not just a table. So I will claim it as my own, though I'm sure it is simple enough to have been used before

I haven't tested it to confirm what happens with ties by the way.

SELECT T1.org_no, T1.date
FROM YourTable AS T1
WHERE 6 >= (SELECT COUNT(*)
FROM YourTable AS T2
WHERE T1.date <= T2.date
AND T1.org_no = T2.org_no
GROUP BY org_no
)
ORDER BY org_no, date desc


[sig][/sig]