1

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem:

I have a table: Query with columns startIndex and endIndex (of type Int(20)) defining range

Here is example subset of our table

CREATE TABLE IF NOT EXISTS `query` ( `id` int(20) unsigned NOT NULL, `indexStart` int(20) unsigned NOT NULL DEFAULT '0', `indexEnd` int(20) unsigned NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `query` (`id`, `indexStart`, `indexEnd`) VALUES (1, 1, 5), (2, 4, 7), (3, 7, 9), (4, 12, 15), (5, 15, 16), (6, 16, 17);

+----+------------+----------+ | id | indexStart | indexEnd | +----+------------+----------+ | 1 | 1 | 5 | | 2 | 4 | 7 | | 3 | 7 | 9 | | 4 | 12 | 15 | | 5 | 15 | 16 | | 6 | 16 | 17 | +----+------------+----------+

The output of query should be

+----+------------+----------+--+ | id | indexStart | indexEnd | | +----+------------+----------+--+ | 3 | 7 | 9 | | | 6 | 17 | 17 | | +----+------------+----------+--+

I want to find the min max solution - the last record of the first overlapping group.

Overlapping group should be defined by records that are indexEnd >= indexStart of another record in the group

In the example subset, I should get the record with id 3 when I execute the desired SQL query,

Because the previous records for a group are overlapping indexStart and indexEnd, and the record with index 4 have indexStart 12 which is greater than the previous record's indexEnd 9 so a gap between records is formed, so the record with id 3 is the last record of first overlapping group.

  • If you edit your question to include some code to show what you have tried and what the answer should look like, it would make it easier to respond. – RLF Oct 16 '15 at 13:25
  • I absolutely have no idea how to approach to this problem I had idea to select distinct select distinct * from ( select min(least(table1, indexStart)), max(greatest(table2, indexEnd)) or something like that but I don't know – Danny Lazarov Oct 16 '15 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.