CODEWITHSUNDEEP
sqlsql-servervb.net
Howard Hee
Howard Hee

Reputation: 929

Find missing number from not sequence number

I have a database with 5 columns (A1,A2,A3,A4,A5) which store 5 numbers.

The 5 numbers are "1,2,3,4,5". 

A1     A2     A3     A4     A5
-------------------------------
2      4      5      Null  Null

I want get the missing number which is "1" and "3". How do I find the missing number from 5 numbers?

Upvotes: 0

Views: 318

Answers (4)

Hannah Vernon
Hannah Vernon

Reputation: 3472

An elegant solution that is extensible beyond the 5 values presented in your question makes use of a numbers table (as shown in the link, a numbers table is invaluable for a multitude of tasks).

Here's the setup for the numbers table, which in this case is limited to 256 numbers since you're using a small range in the question.

/* 
    create a numbers table with numbers from 0 to 
    255 (the range of the tinyint data type).
*/
DROP TABLE IF EXISTS [#numbers];
GO

CREATE TABLE [#numbers]
(
    [n] tinyint NOT NULL
);

WITH [numbers]
AS
(
    SELECT [v].[n]
    FROM
    (
        VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
    ) [v]([n])
)
INSERT INTO [#numbers]
(
    [n]
)
SELECT TOP(255)
    [number] = 
                  ([n1].[n] * 100)
                + ([n2].[n] * 10) 
                + ([n3].[n] * 1)
FROM 
    [numbers] [n1]
    CROSS JOIN [numbers] [n2]
    CROSS JOIN [numbers] [n3]
ORDER BY
      ([n1].[n] * 100)
    + ([n2].[n] * 10) 
    + ([n3].[n] * 1);

Next, we'll create the table you mention in your question:

DROP TABLE IF EXISTS [#rows];
CREATE TABLE [#rows]
(
      [a1]  tinyint  NULL
    , [a2]  tinyint  NULL
    , [a3]  tinyint  NULL
    , [a4]  tinyint  NULL
    , [a5]  tinyint  NULL
);

Here, we populate the row with your values:

INSERT INTO [#rows] ([a1], [a2], [a3])
VALUES (2, 4, 5);

The contents of the [#rows] table:

a1 a2 a3 a4 a5
2 4 5 NULL NULL

I'm going to use an UNPIVOT clause to turn columns into rows. For clarity, this is what the UNPIVOT looks like on its own:

SELECT 
    [upv].[value]
FROM
(
    SELECT *
    FROM [#rows]
) [src]
UNPIVOT
(
    [value] FOR [column] IN ([a1], [a2], [a3], [a4], [a5])
) [upv]
ORDER BY
    [upv].[value];

The intermediate results of the UNPIVOT query:

value
2
4
5

As you can see, we're missing the values 1 and 3. Here's where we display the missing values by combining the results of the UNPIVOT with an EXCEPT T-SQL operator against the [#numbers] table to show the missing values:

SELECT
    [missing_number] = [n].[n]
FROM
    [#numbers] [n]
WHERE 
    [n].[n]     > 0
    AND [n].[n] <= 5
EXCEPT
SELECT 
    [upv].[value]
FROM
(
    SELECT *
    FROM [#rows]
) [src]
UNPIVOT
(
    [value] FOR [column] IN ([a1], [a2], [a3], [a4], [a5])
) [upv];

The EXCEPT operator shows distinct results from the left side of the operator that do not exist in the results from the right side of the operator. The results are:

missing_number
1
3

Upvotes: 1

peterm
peterm

Reputation: 92785

You can do this

WITH sequence AS
(
  SELECT 1 n UNION ALL
  SELECT n + 1 FROM sequence WHERE n < 5
)
SELECT n
  FROM sequence s LEFT JOIN table1 t
    ON s.n IN (t.a1, t.a2, t.a3, t.a4, t.a5)
 WHERE t.a1 IS NULL

Output:

| N |
|---|
| 1 |
| 3 |

Here is SQLFiddle demo

Upvotes: 2

John Bingham
John Bingham

Reputation: 2006

you'll need a table of integers from 1 to (in this case) 5:

DECLARE @ints table (n int);
INSERT @ints VALUES (1), (2), (3), (4), (5);

second, we get the numbers in the table row into a single comparable set:

SELECT x INTO #all FROM (
    SELECT A1 as x FROM myTable WHERE ID = myRow
    UNION ALL
    SELECT A2 as x FROM myTable WHERE ID = myRow
    UNION ALL
    SELECT A3 as x FROM myTable WHERE ID = myRow
    UNION ALL
    SELECT A4 as x FROM myTable WHERE ID = myRow
    UNION ALL
    SELECT A5 as x FROM myTable WHERE ID = myRow
    ) y

then you can derive the answer:

SELECT @ints.n
FROM @ints left join #all on @ints.n = #all.x
WHERE #all.x is null
ORDER BY 1

Upvotes: 0

Amit Singh
Amit Singh

Reputation: 8109

Select Replace(Replace(Replace(Replace(
Replace('12345',(Cast(Coalesce(A5,0) as varchar(1))),''),
(Cast(Coalesce(A4,0) as varchar(1))),''),
(Cast(Coalesce(A3,0) as varchar(1))),''),
(Cast(Coalesce(A2,0) as varchar(1))),''),
(Cast(Coalesce(A1,0) as varchar(1))),'') from Table1

Sql Fiddle Demo

Upvotes: 2

Related Questions