Re: Complex (for me) SQL question -- MIDRANGE-L

× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

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

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.