Subtracting count of rows in subquery from current query

Question

In SQL Server 2005, given the following resultset

ID    |   InstanceNumber     |   IsArchived

5000  |         1            |     True
8347  |         2            |     True
9343  |         3            |     False
11048 |         4            |     False

What I would like to return is this:

ID    |   InstanceNumber     |   IsArchived

9343  |         1            |     False
11048 |         2            |     False

where the rows with "IsArchived" that is false are returned, but subtracting the max InstanceNumber column from the resultset.

Here is an example SQL statement that returns the behavior I'm looking for:

DECLARE @tbl TABLE
(ID INT NOT NULL, InstanceNumber INT NOT NULL, IsArchived BIT NOT NULL)

INSERT INTO @tbl VALUES (5000, 1, 1)
INSERT INTO @tbl VALUES (8347, 2, 1)
INSERT INTO @tbl VALUES (9343, 3, 0)
INSERT INTO @tbl VALUES (11048, 4, 0)

SELECT ID, InstanceNumber - (SELECT MAX(InstanceNumber) FROM @tbl WHERE IsArchived = 1), IsArchived
FROM @tbl
WHERE IsArchived = 0

Is this the most efficient way to do this or is there another way that this same behavior can be achieved? I have additional where clauses that need to go into the full statement (like 5-6 statements) and I want to avoid having to declare them 2X, once for returning the max instance that's archived, and for the resultset filtering.

EDIT To clarify the requirement of the query, the column "InstanceNumber" could skip numbers. so there could be a record for InstanceNumber = 6 without returning one for 5, so not all records returned will be sequential.

Answer 1

In my test, the explain plan was identical between your version using a subselect and my version using a CROSS JOIN:

    SELECT x.id, 
           x.instancenumber - y.max_num AS instancenumber, 
           x.isarchived
      FROM @tbl x
CROSS JOIN (SELECT MAX(InstanceNumber) AS max_num 
              FROM @tbl 
             WHERE IsArchived = 1) y
     WHERE x.isarchived = 0

Answer 2

Try this:

SELECT
   ID,
   InstanceNumber = Row_Number() OVER (ORDER BY InstanceNumber),
   IsArchived
FROM @tbl
WHERE IsArchived = 0

One question, though, is what if there is a gap in the archived data?

INSERT INTO @tbl VALUES (5000, 1, 1)
INSERT INTO @tbl VALUES (8347, 2, 0)
INSERT INTO @tbl VALUES (9343, 3, 1)
INSERT INTO @tbl VALUES (11048, 4, 0)

What results do you want in this case? Your current query gives the InstanceNumber as -1 and 1. My query above gives InstanceNumber 1 and 2. Another possible answer is to return InstanceNumber 1 and 3 (representing the InstanceNumber step of 2 between the 8347 and 11048).

Update

So if I'm understanding correctly about the possibility for gaps, you need to change your query to handle the following case:

INSERT INTO @tbl VALUES (5000, 1, 1)
INSERT INTO @tbl VALUES (8347, 2, 1)
INSERT INTO @tbl VALUES (9343, 4, 0)
INSERT INTO @tbl VALUES (11048, 5, 0)

SELECT
   ID,
   NewInstanceNumber = InstanceNumber + 1
      - (SELECT Min(InstanceNumber) FROM @tbl WHERE IsArchived = 0),
   IsArchived
FROM @tbl
WHERE IsArchived = 0

So that numbering always begins at 1. You could also try this:

SELECT
   ID,
   NewInstanceNumber = InstanceNumber + 1 - Min(InstanceNumber) OVER (),
   IsArchived
FROM @tbl
WHERE IsArchived = 0

But I don't know if that will be better or worse than your current query.

Answer 3

SELECT id,
       instancenumber -
           MAX(CASE IsArchived WHEN true THEN instancenumber ELSE 0 END)
           OVER () as NewInstanceNumber,
       false AS IsArchived
FROM @tbl
WHERE IsArchived = false

I haven't tested this at all. It was just an idea.

Bit of a warning about the other replies:
Using a CROSS JOIN or a subselect can potentially cause issues in a rare case, when a new record with IsArchived=true is inserted mid query (or an existing record is changed from IsArchived=false to IsArchived=true).

If SELECT MAX(InstanceNumber) part of the query is proceseed first, the main select part of the query may be subtracting a value that is no longer the MAX(InstanceNumber) at that time.

With an aggregate window function MAX() OVER(), the actual data is scanned only once, which prevents this problem altogether.