To see what's missing, you can compare to a complete list.
There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums.
Helper table to provide numbers from 0-9:
CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Building on that, generate a complete range of numbers:
SELECT i1.i * 10 + i0.i AS client_code
FROM int10 i1, int10 i0 -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max
Now you can generate the list of missing numbers, exactly as requested:
SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM (
SELECT i1.i * 10 + i0.i AS client_code
FROM int10 i1, int10 i0 -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
) n
LEFT JOIN table_a a USING (client_code)
WHERE a.client_code IS NULL;
Key elements are the LEFT JOIN and the aggregate function GROUP_CONCAT().
To get full list of codes / names including missing codes, as may be your ultimate goal:
SELECT n.client_code, a.client_name
FROM (
SELECT i1.i * 10 + i0.i AS client_code
FROM int10 i1, int10 i0 -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
) n
LEFT JOIN table_a a USING (client_code)
ORDER BY n.client_code;
-> SQLfiddle demo.
You can use CROSS APPLY to "unpivot" multiple columns. This query will give you a list of orders (unpivoted). I've added a third column, which is the LEADing (i.e. next) OrderID, if there is any:
SELECT o.SellerID, x.OrderID,
LEAD(x.OrderID, 1) OVER (
PARTITION BY o.SellerID
ORDER BY x.OrderID) AS _nextOrderID
FROM Orders AS o
CROSS APPLY (
SELECT o.OrderID
UNION ALL
SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
) AS x(OrderID);
This gives you:
SellerID OrderID _nextOrderID
---------- ----------- ------------
seller1 123453 123455
seller1 123455 123456
seller1 123456 123457
seller1 123457 123458
seller1 123458 123459
seller1 123459 123460
seller1 123460 123460
seller1 123460 123463
seller1 123463 123466
seller1 123466 123470
seller1 123470 NULL
seller2 234567 234568
seller2 234568 234569
seller2 234569 234570
seller2 234570 234570
seller2 234570 234572
seller2 234572 NULL
Now, if we subtract the OrderID from the _nextOrderID, we get the gap (how many orders are "missing"):
SELECT o.SellerID, x.OrderID,
LEAD(x.OrderID, 1) OVER (
PARTITION BY o.SellerID
ORDER BY x.OrderID)-x.OrderID AS _gap
FROM Orders AS o
CROSS APPLY (
SELECT o.OrderID
UNION ALL
SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
) AS x(OrderID);
.. which looks something like this:
SellerID OrderID _gap
---------- ----------- -----------
seller1 123453 2
seller1 123455 1
seller1 123456 1
seller1 123457 1
seller1 123458 1
seller1 123459 1
seller1 123460 0 <- 0 because you have two OrderID=123460
seller1 123460 3
seller1 123463 3
seller1 123466 4
seller1 123470 NULL
seller2 234567 1
seller2 234568 1
seller2 234569 1
seller2 234570 0
seller2 234570 2
seller2 234572 NULL
So where _gap<=1, we don't want to return any rows. Where _gap=4, we want to insert 3 rows, from OrderID+1 to OrderID+3. There are a few ways to do this, but I'm going to stick with CROSS APPLY here as well.
I'm putting the query above in a subquery (called sub in my example), and for each row in that result, I'm going to CROSS APPLY any dummy table. If you expect large gaps, you may want to create a separate table with a single IDENTITY column for this purpose, but I'm just going to use the Orders table along with a ROW_NUMBER():
SELECT sub.SellerID, sub.OrderID+n.rownum AS OrderID
FROM (
SELECT o.SellerID, x.OrderID,
LEAD(x.OrderID, 1) OVER (
PARTITION BY o.SellerID
ORDER BY x.OrderID)-x.OrderID AS _gap
FROM Orders AS o
CROSS APPLY (
SELECT o.OrderID
UNION ALL
SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
) AS x(OrderID)
) AS sub
CROSS APPLY (
--- For each row in sub, where _gap>1, return
--- (_gap) number of rows, starting with 1, 2, 3, ..., (_gap-1).
SELECT TOP (sub._gap-1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum
FROM Orders -- or any dummy table
) AS n
--- Only where there's actually a gap:
WHERE sub._gap>1;
Finally, at the top, we add n.rownum to sub.OrderID to get the missing OrderID.
Here's the final output:
SellerID OrderID
---------- --------------------
seller1 123454
seller1 123461
seller1 123462
seller1 123464
seller1 123465
seller1 123467
seller1 123468
seller1 123469
seller2 234571
Best Answer
In postgresql: