RE: [EXTERNAL]Re: Deleting duplicates from table -- MIDRANGE-L

You could select the records that you want to keep and place them in a separate table, truncate the original table, and then insert the records you selected to keep into the original table.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vinay Gavankar
Sent: Wednesday, November 6, 2024 9:20 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL]Re: Deleting duplicates from table

[You don't often get email from vinaygav@xxxxxxxxx. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

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.

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%2Fsupport%2Fpages%2Fsql-reference-cross-platform-development-v
ersion-7&data=05%7C02%7Cbrentreber%40afni.com%7Cfcb4e2529c91436e59a408
dcfe769429%7Cd80d1b57a9af40a9a35b060795e6ce43%7C0%7C0%7C63866503247833
8585%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBT
iI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=%2B6eDk9xlSzwRiwkXGxd%2BO
D1pXxgkvblasuNkp4DMFBU%3D&reserved=0

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%2Fdocs%2Fen%2Fdb2%2F11.5%3Ftopic%3Dstatements-delete&data=05%7
C02%7Cbrentreber%40afni.com%7Cfcb4e2529c91436e59a408dcfe769429%7Cd80d1
b57a9af40a9a35b060795e6ce43%7C0%7C0%7C638665032478365600%7CUnknown%7CT
WFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI
6Mn0%3D%7C0%7C%7C%7C&sdata=Za9D7j0CNKB2VWILsytfnh88BodMXsqwqXSeECqY3UU
%3D&reserved=0 )

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://li/
sts.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cbr
entreber%40afni.com%7Cfcb4e2529c91436e59a408dcfe769429%7Cd80d1b57a9a
f40a9a35b060795e6ce43%7C0%7C0%7C638665032478382116%7CUnknown%7CTWFpb
GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6M
n0%3D%7C0%7C%7C%7C&sdata=EAKQ7p4g03zhmXHIao2Yh9Yf9HpcdPAxIYZklCfdTUw
%3D&reserved=0 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://list/
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cbrentr
eber%40afni.com%7Cfcb4e2529c91436e59a408dcfe769429%7Cd80d1b57a9af40a9a
35b060795e6ce43%7C0%7C0%7C638665032478420095%7CUnknown%7CTWFpbGZsb3d8e
yJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%
7C%7C%7C&sdata=JzsAlvhyEvrJHwTYxoXpM5CDaNmVKQhJCiQniTdfwH4%3D&reserved
=0 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.