Sql: Select a List of Numbers from "Nothing"

SQL: Select a list of numbers from nothing

Thanks for all answers!
Following the discussion I realized that using a numbers table is not too complicated and works well and fast on both/many platforms:

CREATE TABLE integers (i integer);
INSERT INTO integers (i) VALUES (0);
INSERT INTO integers (i) VALUES (1);
...
INSERT INTO integers (i) VALUES (9);
SELECT (hundreds.i * 100) + (tens.i * 10) + units.i AS x
FROM integers AS units
  CROSS JOIN integers AS tens
  CROSS JOIN integers AS hundreds

You just create this table once and can use it whenever you need a range of numbers.

SELECT those not found in IN() list

You can use a derived table or temporary table for example to hold the list of CustomerId then find the non matching ones with EXCEPT.

The below uses a table value constructor as a derived table (compatible with SQL Server 2008+)

SELECT CustomerId
FROM   (VALUES(1),
              (79),
              (14),
              (100),
              (123)) V(CustomerId)
EXCEPT
SELECT CustomerId
FROM   Customers 

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

Get all Numbers that are not existing from a Microsoft SQL-VarChar-Column

DECLARE @Table AS TABLE
(
    Id VARCHAR(5)
)

INSERT INTO @Table
VALUES
 ('1')
,('3')
,('5')
,('7')
,('10')

DECLARE @Range AS TABLE
(
    RangeId VARCHAR(10)
)

INSERT INTO @Range
SELECT TOP (1000000) n = CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1)

select 
    MissingId = RangeId
from 
    @Range AS R
    LEFT OUTER JOIN @Table AS T ON T.Id = R.RangeId
WHERE
    CONVERT(INT,R.RangeId) <= (SELECT MAX(CONVERT(INT,Id)) FROM @Table)
    AND T.Id IS NULL
order by MissingId

Sql select with Not exists

If I understand correctly, you want:

SELECT a.* 
FROM TABLE_A a
WHERE NOT (a.LIST = 'No' AND a.VEHICLE = 'ABC ') OR
      NOT EXISTS (SELECT 1
                  FROM TABLE_A a2
                  WHERE a2.position = a.position AND
                        NOT (a.LIST = 'No' AND a.VEHICLE = 'ABC')
                 );

That is, select all rows that are not 123/No. Then select all rows for positions that only consist of such values.

You can filter for a particular position in the outer query just by adding AND position = ?.

If you want to do this for one position and only want one row, then a simpler formulation is:

select a.*
from table_a a
where a.position = XXX
order by (case when a.LIST = 'No' and a.VEHICLE = 'ABC' then 1 else 2 end) desc
fetch first one row only;

---

Related Topics