Last active
December 10, 2015 21:59
-
-
Save Danack/4498861 to your computer and use it in GitHub Desktop.
How to order within blocks
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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