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
midrange.com
