Re: Complex (for me) SQL question

Mike,

I finally got a chance to try this. Thank you so much! That worked like a
charm. I would have never figured this out on my own. I've seen LATERAL
discussed a few times here, but could not wrap my head around it. Having
my own real world example helps, but I'll definitely take a close look at
your document. (I noticed it is "Part 1". Do you have other parts
available?)

- Dan

On Mon, Jan 18, 2016 at 4:24 PM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

Hi Dan,

LATERAL joins make that simple:
--------------------------------------------------------------------
with CTE_DUPLICATES as (
select PRPRDN
from HPRDFA
group by PRPRDN
having count(*) > 1
)

select DETAIL.*
from CTE_DUPLICATES D

cross join lateral (
select H.*
from HPRDFA H
where H.PRPRDN = D.PRPRDN
order by H.PRDATER
fetch first 2 rows only
) as DETAIL

order by DETAIL.PRPRDN, DETAIL.PRDATER
--------------------------------------------------------------------

LATERAL joins => learn 'em, love 'em, use 'em...:)

The ROW_NUMBER() function can also handle that task nicely.

Mike