Re: Complex (for me) SQL question

Somewhere in there is a need for an order by PRDATER (process date).
But the analytical
row_number() over...
should be the light that clicks on for the OP.

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





From: Michael Schutte <mschutte369@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 01/18/2016 04:05 PM
Subject: Re: Complex (for me) SQL question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Add () to the row_number, remove group by from the over(...)

On Mon, Jan 18, 2016 at 3:53 PM, Michael Schutte <mschutte369@xxxxxxxxx>
wrote:

Oops, didn't finish reading.

with sum1 as (
select PRPRDN from HPRDFA
group by PRPRDN
HAVING COUNT(1) > 1
), results as (
select HPRDFA.*, row_number over(partition by PRPRDN group by PRPRDN)
row_number_by_item
from HPRDFA
where PRPRDN in (select PRPRDN from sum1) )
select * from results
where row_number_by_item <= 2

On Mon, Jan 18, 2016 at 3:51 PM, Michael Schutte <mschutte369@xxxxxxxxx>
wrote:

Would this suffice?

select PRPRDN from HPRDFA
group by PRPRDN
HAVING COUNT(1) > 1
order by PRPRDN

Or
with sum1 as (
select PRPRDN from HPRDFA
group by PRPRDN
HAVING COUNT(1) > 1
)
select * from HPRDFA
where PRPRDN in (select PRPRDN from sum1)



On Mon, Jan 18, 2016 at 3:33 PM, Dan <dan27649@xxxxxxxxx> wrote:

I have a table with duplicate (by part number) rows. The following

query

lists only those rows for which duplicates exist:

with sum1 as (
select PRPRDN, count(*) as count1 from HPRDFA
group by PRPRDN
order by PRPRDN)
select * from HPRDFA d
where exists (select * from sum1 s where s.PRPRDN = d.PRPRDN and
s.count1 > 1)
order by PRDATER

I need to have this query list only the first two records for each

part

number (field PRPRDN), ordered by PRDATER (process date). I'm

thinking

somewhere along the lines of "fetch first 2 rows only", but this will
return only two records for the entire query, not two records for each
part
number.

References to RTFM and other advice welcomed.

- Dan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.