Re: Help Needed With Delete Statement

From: Joseph Ranseth <jransethNO_SPAM_at_hotmail.com>
Date: Thu, 18 Jan 2001 13:54:29 -0600
Message-ID: <aVH96.1041$wb7.21031@news1.mts.net>

Try somthing like this...

SELECT date
FROM (SELECT date, rownum as rank

            FROM my_table
            ORDER BY date )

WHERE rank = 30
;
But be sure to fetch this value into a local variable, and then execute the delete statement as follows:

DELETE FROM my_table
WHERE date > var
;
(var is the variable you fetched the result from the first query into)

HTH,
J

"Dave Sutton" <dpsutton_at_marchsystems.co.uk> wrote in message news:9470th$j19$1_at_newsreaderm1.core.theplanet.net...
> Hi,
>
> I hope someone out there can help me with this SQL problem!
>
> I have a table of audit records which contains a date field.
>
> What I need is an SQL statement to delete all but the 30 most recent
> records, based on this date field.
>
> I tried using the clause:
> WHERE ROWNUM > 30
>
> but it appears you can't use "rownum >" and anyway, rownum does not always
> take into account the order clause.
>
> Any help would be greatly appreciated.
>
>
> Dave
>
>
Received on Thu Jan 18 2001 - 13:54:29 CST