Postgres - Query distinct/longest date ranges

Maia
1

I'm looking to apply some logic that returns the distinct/longest date ranges in a particular table. In the example below, PKID 1 contains the date ranges in 2-4, then is proceeded by 5. I can't think of a good way to return 1 and 5 without writing multiple 

MIN()

 

 and 

MAX()

 

 aggregates, comparing lengths of time, or writing a 10 line 

CASE

 

 statement with 

LAG()

 functions. Is there an easier way to return PKID 1 and 5 without doing 20 levels of logic tests?

I've found a few posts that come close to this problem but aren't the same. I can't think of a way to implement the 

OVERLAP

 function in this instance, but enjoyed learning about it

2

Hi @vivansai4​ and thanks for posting!

It's possible to interpret your question more than one way, depending on what you're trying to achieve. However, it looks like this same question may have already been answered over on StackOverflow.

Is that your question, and are you happy with the answer? If not, could you give an example of the input data and results you're looking for?

Happy to help if it's still needed!

Many thanks,

Claire