Skip to content

Instantly share code, notes, and snippets.

@Danack
Last active December 10, 2015 21:59
Show Gist options
  • Save Danack/4498861 to your computer and use it in GitHub Desktop.
Save Danack/4498861 to your computer and use it in GitHub Desktop.
How to order within blocks
// Given the following table:
//
// --------------
// | id | event |
// --------------
// | 00 | start |
// | 01 | data |
// | 02 | end |
// | 03 | data |
// | 04 | data |
// | 05 | start |
// | 06 | data |
// | 07 | data |
// | 08 | end |
// | 09 | data |
// | 10 | start |
// | 11 | data |
// | 12 | end |
// --------------
//
// How would I write a SQL query to return all of the rows between "start" and "end" rows?
// Assuming data is in a table called test.tce
CREATE temporary TABLE lookup
(id INT,
startID INT,
endID INT)
ENGINE = MEMORY;
CREATE temporary TABLE lookupSorted
( indexID int(10) unsigned NOT NULL auto_increment,
id INT,
startID INT,
endID INT,
PRIMARY KEY (`indexID`)
)
ENGINE = MEMORY;
CREATE temporary TABLE lookupSorted2
( indexID int(10) unsigned NOT NULL auto_increment,
id INT,
startID INT,
endID INT,
PRIMARY KEY (`indexID`)
)
ENGINE = MEMORY;
insert into lookup select id, id as startID, -99 as endID from test.tce where event = 'start';
insert into lookup select id, -99 as startID, id as endID from test.tce where event = 'end';
insert into lookupSorted (id, startID, endID) select id, startID, endID from lookup order by id;
insert into lookupSorted2 (id, startID, endID) select id, startID, endID from lookup order by id;
#select * from lookupSorted;
CREATE temporary TABLE goodData
( indexID int(10) unsigned NOT NULL auto_increment,
startID INT,
endID INT,
PRIMARY KEY (`indexID`)
)
ENGINE = MEMORY;
CREATE temporary TABLE goodData2
( indexID int(10) unsigned NOT NULL auto_increment,
startID INT,
endID INT,
PRIMARY KEY (`indexID`)
)
ENGINE = MEMORY;
insert into goodData (startID, endID) select (a.startID + 1) as startID, (b.endID - 1) as endID from lookupSorted a
inner join lookupSorted2 b
on a.indexID + 1 = b.indexID
where a.startID != -99
and b.endID != -99;
insert into goodData2 (startID, endID) select (a.startID + 1) as startID, (b.endID - 1) as endID from lookupSorted a
inner join lookupSorted2 b
on a.indexID + 1 = b.indexID
where a.startID != -99
and b.endID != -99;
#select * from goodData;
select a.id, a.event from test.tce a
inner join goodData b on
a.id >= b.startID
inner join goodData2 c
on a.id <= c.endID
and b.indexID = c.indexID;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment