× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Opps..

Should be
row_number() OVER (partition by pgmname order by entrydate DESC) as rownum

Forgot the DESCending.

On Mon, Apr 20, 2015 at 10:02 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

It appears that at least with 7.1, you can delete from a view that uses
ROW_NUMBER()...

create view myview
as ( select
row_number() OVER (partition by pgmname order by entrydate) as
rownum
, a.*
from mytable A
);

delete from myview
where rownumber > 30 and entrydate <= '2015-02-15';

Too bad DB2 for i doesn't appear to support deleting from a CTE...though
LUW apparently does. As does SQL Server,,,


Charles

On Mon, Apr 20, 2015 at 9:37 AM, John R. Smith, Jr. <
smith5646midrange@xxxxxxxxx> wrote:

Although that second line sounds wrong, I think that is a correct
interpretation of the end results.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Charles Wilt
Sent: Monday, April 20, 2015 8:47 AM
To: Midrange Systems Technical Discussion
Subject: Re: Deleting with SQL

So for every program, you'll end up with...
either
- >= 30 entries, starting at 2/15/2015
- <= 30 entries, going back as far as needed? (assuming it's possible
that
you don't have 30 entries currently logged)



Charles

On Mon, Apr 20, 2015 at 7:38 AM, John R. Smith, Jr. <
smith5646midrange@xxxxxxxxx> wrote:

I have a file that is a log of who ran programs on what date that I
need to purge but the purge has dual criteria. I need to purge based
on keeping x number of entries and entries before x date. For
example, I need to keep all entries after 02/15/2015 AND also the last
30 entries. So, if an entry falls inside of either of these criteria,
I keep it. The user is immaterial to the result set.



I'm not sure I have explained that very well but in the event that I
have, can anyone tell me how to do it through SQL?



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.