1

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;
  • 1
    Please post the table definition as create table and some sample data (ideally as insert statement). Even better: create a short example on sqlfiddle.com – a_horse_with_no_name Nov 26 '14 at 13:57

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.