I have a set of records, and I want to get the set of records between two records of the same type.
Consider this sample data:
ID POSITION TYPE
9 1 separator
7 2 one
8 3 three
5 4 one
2 5 separator
4 6 two
3 7 two
6 8 separator
1 9 four
11 10 five
10 11 three
12 12 five
I want to get the records from a specific separator to the next separator or the end of the record set.
Cases:
I can solve Case 1 with a sub-query, like this:
SELECT *
FROM table_name
WHERE position > 5
AND position < (
SELECT MIN(position)
FROM table_name
WHERE position > 5
AND type = 'separator'
)
ORDER BY position ASC
That solution will not work for Case 2, and it seems like there should be a way that doesn't use a sub-query.
SOLUTION
My solution is based on George's answer, and works, but I am concerned about it's potential lack of performance because of the coalesce function and sub-queries.
SELECT *
FROM table_name
WHERE `position` > 5
AND `position` < COALESCE(
(SELECT MIN(`position`)
FROM table_name
WHERE `position` > 5
AND `type` = 'separator'),
(SELECT MAX(`position`) + 1
FROM table_name))
ORDER BY `position` ASC
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `table_name` ADD INDEX `table_name_idx_position` (`position`);
ALTER TABLE `table_name` ADD INDEX `table_name_idx_type_position` (`type`,`position`);
SELECT
*
FROM
table_name
WHERE
table_name.position > 5
AND table_name.position < (
SELECT
MIN(table_name.position)
FROM
table_name
WHERE
table_name.position > 5
AND table_name.type = 'separator'
)
ORDER BY
table_name.position ASC