Re: Deleting duplicates from table

I want to keep the instance where the fldd and flde are highest.

This seems to work (fldd and flde are varchar fields):

delete from myfile a
where a.fldd || a.flde <
(select max( b.fldd || b.flde ) from myfile b
where a.flda = b.flda and a.fldb = b.fldb and a.fldc = b.fldc)



On Wed, Nov 6, 2024 at 10:36 AM Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

Do you want to keep only one instance of the dupes? Or do you want to
sweep them all out?

JS


El mié, 6 nov 2024 a las 9:20, Vinay Gavankar (<vinaygav@xxxxxxxxx>)
escribió:

I tried the below :
delete from myfile a
where rrn(a) >
(select min(rrn(b)) from myfile b
where a.flda = b.flda and a.fldb = b.fldb and a.fldc = b.fldc)

It kind of works, but I have no control over which records are deleted. I
need to keep the record with highest value of fldd and flde (which are
receipt date and time).

Any suggestions?
Vinay



On Wed, Nov 6, 2024 at 9:01 AM Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Allow me to expand on Darren's post - when he says "...an example from
DB2..." I believe he means Db2 for Linux/Unix/Windows - Vinay, the link
that you give has 11.5 in it - that is the version for the LUW version
of Db2 - it is not for IBM i.

The different versions or flavors of Db2 are very similar - IBM has a
website comparing them. Still, there are differences - here is a page
with links to resources -

https://www.ibm.com/support/pages/sql-reference-cross-platform-development-version-7

Regards
Vern

On 11/6/2024 7:21 AM, Darren Strong wrote:

I don't think you can delete from a virtual table like that. You're

looking at an example from DB2, but are probably running this on an IBM

I,

so its DB2 for I, which is different and maddeningly difficult to

Google.

Thanks IBM.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf

Of

Vinay Gavankar

Sent: Tuesday, November 5, 2024 10:33 AM
To: Midrange Systems Technical Discussion

<midrange-l@lists.midrangecom

Subject: Deleting duplicates from table

CAUTION: This email originated from outside of the organization. Do

not

click links or open attachments unless you recognize the sender and

know

the content is safe.

Hi,

I am trying to delete duplicate from a table, using following sql

(based

on example from
https://www.ibm.com/docs/en/db2/11.5?topic=statements-delete )

delete from
(select rownumber() over (partition by flda, fldb, fldc
order by fldd desc,

flde

desc) from myfile) as a(rn)

where rn > 1

I get an error over the first '(' before select:
Token ( was not valid. Valid tokens: <IDENTIFIER>.

What am I doing wrong?

TIA
Vinay
--
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@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.

--
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@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.