I have two tables, one stores structure (using so called parent field), the other entities that can be embeded into that structure. As certain level of structure can contain both entities and deeper elements of structure both tables have sort column (of type integer). Both structure and entities can mix on a given level, so sort numbers are in effect "dispersed" between the two tables, but required to be continous (so 1,2,3,4 and not 1,4,5,9).
The problem is that some app most probably screwed that order, to be not continous and I'd like to fix it. I can create UNION query to inspect order at certain level, but I have to check numbers visually to determine if they are in fact continous (which is error prone). So the question is: can I, using pure SQL, determine where the order is not continous? I guess I could get redundant order numbers (eg. 1,2,2,3) by some wicked GROUP BY. Not sure how to catch "holes" (eg. 1,2,4,5);
EDIT: As requested, below you can find a simplified example of the problem:
CREATE TABLE structure ( structure_id INTEGER, structure_name TEXT, structure_parent INTEGER, structure_sort INTEGER ); CREATE TABLE entity ( entity_id INTEGER, entity_name TEXT, entity_structure INTEGER, entity_sort INTEGER ); INSERT INTO structure VALUES (1, 'root', null, 0); INSERT INTO structure VALUES (2, 's1', 1, 1); INSERT INTO structure VALUES (3, 's2', 1, 3); INSERT INTO structure VALUES (4, 's3', 1, 7); INSERT INTO entity VALUES (1, 'e1', 1, 2); INSERT INTO entity VALUES (2, 'e2', 1, 2); INSERT INTO entity VALUES (3, 'e3', 1, 5); INSERT INTO entity VALUES (4, 'e4', 1, 8);
Sequences, referential integrity and some meaningless columns skiped for brewity. Here is how you can see all the flaws in order numbers:
SELECT structure_id as "item id", structure_name as "item name", 'structure' as "item type", structure_sort as "item sort" FROM structure WHERE structure_parent = 1 UNION SELECT entity_id as "item id", entity_name as "item name", 'entity' as "item type", entity_sort as "item sort" FROM entity WHERE entity_structure = 1 ORDER BY 4;