I now know that I have several duplicates. Is there any way to get a
list of just duplicate records, and their relative record number?
Try:
With x as (Select ORORD#, OTUSM, OTTRNC
From rklib/clspaytpa
Group By ORORD#, OTUSM, OTTRNC
Having Count(*) > 0)
Select rrn(a), a.*
From x join rklib/clspaytpa a on x.ORORD# = a.ORORD#
and x.OTUSM = a.OTUSM
and x.OTTRNC = a.OTTRNC
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
paul.roy@xxxxxxx
Gesendet: Friday, 05.6 2015 14:32
An: Midrange Systems Technical Discussion
Betreff: Re: AW: how to use count in sql
That kind of instruction should do it (not tested)
Select RRN(clspaytpa),ORORD#, OTUSM, OTTRNC from clspaytpa where
concat(orord#, concat( otusm, ottrnc)) in
(
Select concat(orord#, concat(otusm, ottrnc))
From rklib/clspaytpa
Group By ORORD#, OTUSM, OTTRNC
Having Count(*) > 0
)
Paul
From: <mailto:rob@xxxxxxxxx> rob@xxxxxxxxx
To: Midrange Systems Technical Discussion <
<mailto:midrange-l@xxxxxxxxxxxx> midrange-l@xxxxxxxxxxxx>
Date: 05/06/2015 13:41
Subject: Re: AW: how to use count in sql
Sent by: "MIDRANGE-L" < <mailto:midrange-l-bounces@xxxxxxxxxxxx>
midrange-l-bounces@xxxxxxxxxxxx>
Birgitta's right. And I was going to suggest a link at lmgtfy...
Ok, does anyone know that next question we should expect?
No?
Ok, here it is: I now know that I have several duplicates. Is there any
way to get a list of just duplicate records, and their relative record
number?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
<
http://www.dekko.com>
http://www.dekko.com
From: "Birgitta Hauser" < <mailto:Hauser@xxxxxxxxxxxxxxx>
Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
< <mailto:midrange-l@xxxxxxxxxxxx> midrange-l@xxxxxxxxxxxx>
Date: 06/04/2015 09:58 PM
Subject: AW: how to use count in sql
Sent by: "MIDRANGE-L" < <mailto:midrange-l-bounces@xxxxxxxxxxxx>
midrange-l-bounces@xxxxxxxxxxxx>
No need for a CTE! The following statement will do the job.
Select ORORD#, OTUSM, OTTRNC, Count(*)
From rklib/clspaytpa
Group By ORORD#, OTUSM, OTTRNC
Having Count(*) > 0
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [ <mailto:midrange-l-bounces@xxxxxxxxxxxx>
mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Alan
Campin
Gesendet: Thursday, 04.6 2015 23:49
An: Midrange Systems Technical Discussion
Betreff: Re: how to use count in sql
Use a CTE.
With List As( Select ORORD#, OTUSM, OTTRNC, Count(*) as MyCount
From rklib/clspaytpa
Group By ORORD#, OTUSM, OTTRNC) Select *
from List
Where MyCount > 1
I think that is right.
On Thu, Jun 4, 2015 at 2:37 PM, < <mailto:paul.roy@xxxxxxx>
paul.roy@xxxxxxx> wrote:
I would use Group by
Paul
On 4 juin 2015, at 23:31, "Hoteltravelfundotcom" <
<mailto:hoteltravelfun@xxxxxxxxx> hoteltravelfun@xxxxxxxxx>
wrote:
I would like to see a list of duplicate rows according to these 3
fields.
but this is running
how to show me where there is duplicate. there are 100's of
thousands of rows so i cannot go thru on by one.
SELECT otord#, otusrn, ottrnc, count(*) FROM rklib.clspaytpa WHERE
ottrnd > 20150500 ORDER BY otord#, otusrn, ottrnc
HAVING count(*) > 1
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: <mailto:MIDRANGE-L@xxxxxxxxxxxx>
MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: <http://lists.midrange.com/mailman/listinfo/midrange-l>
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
<http://archive.midrange.com/midrange-l>
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: <mailto:MIDRANGE-L@xxxxxxxxxxxx>
MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: <http://lists.midrange.com/mailman/listinfo/midrange-l>
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
<http://archive.midrange.com/midrange-l>
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: <mailto:MIDRANGE-L@xxxxxxxxxxxx>
MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: <
http://lists.midrange.com/mailman/listinfo/midrange-l>
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at <
http://archive.midrange.com/midrange-l>
http://archive.midrange.com/midrange-l.
midrange.com
