× 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.



Thanks Rob: RID is just a sequential "reckey". That SQL worked.

On Tue, May 7, 2019 at 3:05 PM Rob Berendt <rob@xxxxxxxxx> wrote:

Is RID a column in the table (like Infor's FRT table) or is it a use of
the reserved word RID as in

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzscarid.htm

I would almost thing it would have to be a column in that table as
grouping by a relative record number should always be a one row group and
max(dwlineno) would only be the max of a single row. That, and you didn't
specify a parenthetical expression after RID.
select rid(erplxf.frt) as rid, erplxf.frt.rid, erplxf.frt.*
from erplxf.frt
where ropno=5

So, assuming that RID is a column, you might be able to do something like
Delete from dwimages
Where rid, dwlineno not in(
select rid, max(dwlineno)
from dwimages group by rid order by rid)

Yes, IBM adding RID rather late in the game confused some people. I still
remember the people from Infor jotting down notes as I spoke to the Omni
group and mentioned that recent addition to the OS and how it affected our
recompile of one of their programs.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Art
Tostaine, Jr.
Sent: Tuesday, May 7, 2019 2:39 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL Max question

I want to delete a bunch of records. My key is RID, LineNo. I want to
keep only the highest line# for each RID.

This statement gives me the records I want to delete. How do I change it
to Delete instead? Having a hard time searching for what I want.

select rid, max(dwlineno)
from dwimages group by rid order by rid

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com




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.