Choose the longest shared timer

Question

I have a table in which I have an ID binding:

┌────────────────┬──────────────────────┐
│ location_id    | datetime             |
├────────────────┼──────────────────────┤
│ 200333         | 2008-01-01 00:00:00  |
│ 200333         | 2008-01-01 01:00:00  |
│ 200333         | 2008-01-01 02:00:00  |
| ...            | ...                  |
│ 200333         | 2009-10-23 21:00:00  |
│ 200333         | 2009-10-23 22:00:00  |
│ 200333         | 2009-10-23 23:00:00  |
│ 200768         | 2008-06-01 00:00:00  |
│ 200768         | 2008-06-01 01:00:00  |
│ 200768         | 2008-06-01 02:00:00  |
| ...            | ...                  |
│ 200768         | 2009-12-31 00:00:00  |
│ 200768         | 2009-12-31 00:00:00  |
│ 200768         | 2009-12-31 00:00:00  |
└────────────────┴──────────────────────┘

How can I choose the longest time period for these two overlapping sections location_id? In this case, the desired output would be:

┌──────────────────────┬──────────────────────┐
│ start                | end                  |
├──────────────────────┼──────────────────────┤
│ 2008-06-01 00:00:00  | 2009-10-23 23:00:00  |
└──────────────────────┴──────────────────────┘

I can easily get the longest period using MIN()and MAX(), but how would I like to choose the maximum minimum time and minimum maximum dates?

Oh, and this table contains 19 million rows, so bonus points for offers that are quickly executed :)

Answer 1

You can try something

SELECT  MAX(MinDates) MaximumMinDate,
        MIN(MaxDates) MinimumMaxDate
FROM    (
            SELECT  location_ID,
                    MIN([datetime]) MinDates,
                    MAX([datetime]) MaxDates
            FROM    Table
            WHERE   location_ID IN (200333, 200768)
            GROUP BY location_ID
        ) sub

And then just replace the ids with what you need.

Answer 2

, :

SELECT l1.maxtime, l2.mintime FROM 
(SELECT location_id, min(datetime), max(datetime)
FROM table
GROUP BY location_id
) as l1(id, mintime, maxtime)
,
(SELECT location_id, min(datetime), max(datetime)
FROM table
GROUP BY location_id
) as l2(id, mintime, maxtime)
WHERE 
l1.id <> l2.id
HAVING max(l1.maxtime-l2.mintime);