Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL find lowest value not found in two tables

I want to find the lowest missing number from two tables

For Example,
Table1 columna has 1,2,3,8
Table2 columnb has 4,10,11

I want to return 5

I have been able to get the lowest missing number from one table, i.e. 4 from Table1, but every time I try to join or compare to another table all I can manage is to get the next ‘available’ number, i.e. 12.

my sql code:

WITH MissingValues AS (
    SELECT a.bidder_number + 1 AS missing_value
    FROM bidder a
    LEFT JOIN bidder b ON a.bidder_number + 1 = b.bidder_number
    WHERE b.bidder_number IS NULL
     AND a.auctionid = 1)
SELECT MIN(missing_value) AS lowest_missing_value
FROM MissingValues

WHERE missing_value NOT IN ( 
     SELECT permanent_biddernumber FROM customer
     WHERE permanent_biddernumber > 0 AND permanent_biddernumber IS NOT NULL)

;

Tags:

3

Answers


  1. 0
    • I first generate a series of numbers till the max of from both tables.This gives you all the numbers in numbers CTE
    • Then those numbers are compared with the values present in both tables and the min is choosen.

    Fiddle

    WITH RECURSIVE numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM numbers
    WHERE num < (SELECT MAX(GREATEST(t1.columna, t2.columnb)) 
                 FROM Table1 t1, Table2 t2)
)
SELECT MIN(num) AS lowest_missing
FROM numbers
WHERE num NOT IN (SELECT columna FROM Table1
                  UNION 
                  SELECT columnb FROM Table2)
LIMIT 1;
    Login or Signup to reply.
  2. 0

    You can do this pretty simply, by using UNION ALL to union the two tables, then use LEAD to check if the next value for each row is exactly one more.

    WITH unioned AS (
    SELECT t1.id
    FROM Table1 t1

    UNION ALL

    SELECT t2.id
    FROM Table2 t2
),
WithLead AS (
    SELECT u.*,
      LEAD(u.id) OVER (ORDER BY u.id) AS NextId
    FROM unioned u
)
SELECT
  p.id + 1 AS FirstMissing
FROM WithLead p
WHERE (p.NextId > p.id + 1 OR p.NextId IS NULL)
ORDER BY
  p.id
LIMIT 1;

    This results in only a single merged scan of both tables and no joins, and assuming there are indexes there will be no sorts either.

    db<>fiddle

    Login or Signup to reply.
  3. 0

    I would suggest find every missing row for each table separately and then get the min missing one on both tables

    SELECT MIN(missingNumber) as minMissingNumber
FROM(
        (
            WITH RECURSIVE sequence_generator AS (
              SELECT MIN(id) AS n
              FROM Table1
              UNION ALL
              SELECT n + 1
              FROM sequence_generator
              WHERE n < (SELECT MAX(id) FROM Table1)
              )
               SELECT n AS missingNumber
               FROM sequence_generator
               WHERE n NOT IN (SELECT id FROM Table1)
               ORDER BY missingNumber DESC LIMIT 1
        )
        UNION ALL
        (
            WITH RECURSIVE sequence_generator AS (
              SELECT MIN(id) AS n
              FROM Table2
              UNION ALL
              SELECT n + 1
              FROM sequence_generator
              WHERE n < (SELECT MAX(id) FROM Table2)
              )
               SELECT n AS missingNumber
               FROM sequence_generator
               WHERE n NOT IN (SELECT id FROM Table2)
               ORDER BY missingNumber ASC LIMIT 1
        ) 
    ) x;

    See example

    Note

    If there is no missing row, or number, then I would expect to get the
    next ‘available’ number not in the columns. For example, t1 has 1,2,3,
    and t2 has 4,5,6 then I would want to get 7

    Above answer will return null since there is no missing row

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search