Re: SQL Max question

["Art Tostaine, Jr." <atostaine@xxxxxxxxx>]

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