Continuous sequences in SQL
The presence of a table with the following fields:
Order, group, sequence
it is required that all orders in a given group form a continuous sequence. For example: 1,2,3,4 or 4,5,6,7. How can I check, using a single SQL query, which orders do not match this rule? Thanks.
Example data:
Order Group Sequence
1 1 3
2 1 4
3 1 5
4 1 6
5 2 3
6 2 4
7 2 6
Expected result:
Order
5
6
7
Also accepted if the query only returns a group that has the wrong sequence, 2 for example data.
a source to share
How about this?
select a group from the table
group by group
(Sequence) <= max (Sequence) -min (Sequence)
[Edit] This assumes the Sequence does not allow duplicates in a specific group. Better to use:
count! = Max - min + 1
[Edit again] D'O, still not perfect. Another request, to clean up duplicates, will take care of this.
[Edit last] The original query worked fine in sqlite and this is what I had for a quick test. It is much more forgiving than SQL Server. Thanks Bell for the pointer.
a source to share
Personnel. I think I would consider rethinking this requirement. It is the nature of relational databases that sequence gaps can easily be caused by rolling back records. For example, suppose an order starts creating four items in it, but one of them fails for some rason and rolls back. If you pre-computed the sequences by hand, then you have a gap that is not rolled back last. In other scenarios, you might get a break due to multiple users searching for sequence values at approximately the same time, or if the customer deleted one record from the order at the last moment. What do you honestly want to get out of the continuous sequences that you don't get from parenting with your kids?
a source to share
This SQL selects orders 3 and 4, which have no contiguous sequences.
DECLARE @Orders TABLE ([Order] INTEGER, [Group] INTEGER, Sequence INTEGER)
INSERT INTO @Orders VALUES (1, 1, 0)
INSERT INTO @Orders VALUES (1, 2, 0)
INSERT INTO @Orders VALUES (1, 3, 0)
INSERT INTO @Orders VALUES (2, 4, 0)
INSERT INTO @Orders VALUES (2, 5, 0)
INSERT INTO @Orders VALUES (2, 6, 0)
INSERT INTO @Orders VALUES (3, 4, 0)
INSERT INTO @Orders VALUES (3, 6, 0)
INSERT INTO @Orders VALUES (4, 1, 0)
INSERT INTO @Orders VALUES (4, 2, 0)
INSERT INTO @Orders VALUES (4, 8, 0)
SELECT o1.[Order]
FROM @Orders o1
LEFT OUTER JOIN @Orders o2 ON o2.[Order] = o1.[Order] AND o2.[Group] = o1.[Group] + 1
WHERE o2.[Order] IS NULL
GROUP BY o1.[Order]
HAVING COUNT(*) > 1
a source to share
So your table is in the form
Order Group Sequence
1 1 4
1 1 5
1 1 7
.. and you want to know what 1,1,6 is missing?
FROM
select
min(Sequence) MinSequence,
max(Seqence) MaxSequence
from
Orders
group by
[Order],
[Group]
you can find out the limits for a given order and group.
Now you can simulate the correct data using the special numbers table , which contains only every number you could ever use for a sequence. Here is a good example of such a table of numbers. It doesn't matter how you create it, you can also create an excel file with all numbers from x to y and import that excel sheet.
In my example I am assuming a table of numbers like this called "Numbers" with only one column "n":
select
[Order],
[Group],
n Sequence
from
(select min(Sequence) MinSequence, max(Seqence) MaxSequence from [Table] group by [Order], [Group]) MinMaxSequence
left join Numbers on n >= MinSequence and n <= MaxSequence
Place this SQL in a new view. In my example, I will call the view "vwCorrectOrders".
This gives you data in which the sequences are continuous. Now you can join this data with the original data to find out which sequences are missing:
select
correctOrders.*
from
vwCorrectOrders co
left join Orders o on
co.[Order] = o.[Order]
and co.[Group] = o.[Group]
and co.Sequence = o.Sequence
where
o.Sequence is null
Gotta give you
Order Group Sequence
1 1 6
a source to share
After a while, I came up with the following solution. It seems to work, but it is very inefficient. Please add suggestions for improvement.
SELECT OrdMain.Order
FROM ((Orders AS OrdMain
LEFT OUTER JOIN Orders AS OrdPrev ON (OrdPrev.Group = OrdMain.Group) AND (OrdPrev.Sequence = OrdMain.Sequence - 1))
LEFT OUTER JOIN Orders AS OrdNext ON (OrdNext.Group = OrdMain.Group) AND (OrdNext.Sequence = OrdMain.Sequence + 1))
WHERE ((OrdMain.Sequence < (SELECT MAX(Sequence) FROM Orders OrdMax WHERE (OrdMax.Group = OrdMain.Group))) AND (OrdNext.Order IS NULL)) OR
((OrdMain.Sequence > (SELECT MIN(Sequence) FROM Orders OrdMin WHERE (OrdMin.Group = OrdMain.Group))) AND (OrdPrev.Order IS NULL))
a source to share