Finding non-contiguous number using SQL

Question

I have a question about fixing a non-contiguous data that I've:

Let's say I have this table:

Table A:

Name Description Value
ABC  Day         1
ABC  Night       3
ABC  Week        7
XYZ  FFF         2
XYZ  GGG         3
XYZ  VVV         5
AAA  BBB         1
AAA  DDD         2
AAA  CCC         3

Now for name "ABC" and "XYZ" I've non-contiguous number. I need to find the number that are missing for those two name and make them contiguous So that

Name Description Value
ABC  Day         1
ABC  Night       2
ABC  Week        3
XYZ  FFF         2
XYZ  GGG         3
XYZ  VVV         4

Any idea

Answer 1

@ThomasRushton's answer is correct (+1) but can be refactored down to a more concise version:

DECLARE @NonContigData TABLE
    (Name char(3),
     Description varchar(max),
     value int)
INSERT  INTO @NonContigData
VALUES  ('abc', 'day', 1),
        ('abc', 'night', 3),
        ('abc', 'week', 7),
        ('xyz', 'fff', 2),
        ('xyz', 'ggg', 3),
        ('xyz', 'vvv', 5),
        ('aaa', 'bbb', 1),
        ('aaa', 'ddd', 2),
        ('aaa', 'ccc', 3);
WITH    prepdata
          AS (SELECT    *,
                        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY value) AS newvalue
              FROM      @NonContigData AS NCD)
    UPDATE  prepdata
    SET     value = newvalue
    WHERE   value <> newvalue

;
SELECT * FROM @NonContigData AS NCD

I have wrapped the original data into a CTE prepdata which assigns the column newvalue with the contiguous numbering desired (preserving the ordering using the current non-contiguous value). An update is then run against the CTE, updating the entries where value is non contiguous.

This saves a join and just resorts the data, so should be a little better on resources (don't forget to test though!)

Answer 2

0

Try this:

-- using @NonContigData as a table to show the principle.  
-- Replace this with your table name.
declare @NonContigData table (Name char(3), Description varchar(max), value int)
insert into @NonContigData values 
('abc', 'day', 1), ('abc', 'night', 3), ('abc', 'week', 7),
('xyz', 'fff', 2), ('xyz', 'ggg', 3), ('xyz', 'vvv', 5),
('aaa', 'bbb', 1), ('aaa', 'ddd', 2), ('aaa', 'ccc', 3)

-- And here's how the update works:
UPDATE @NonContigData 
SET value = ncdnew.NewValue 
FROM
@NonContigData ncd1
INNER JOIN (SELECT Name, Description, value, 
           (ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Value))
            + (SELECT MIN(Value) FROM @NonContigData ncd2 WHERE ncd2.Name = ncd1.name) - 1 
            AS NewValue FROM @NonContigData ncd1) AS ncdnew
ON ncd1.Name = ncdnew.Name AND ncd1.Description = ncdnew.Description AND ncd1.value = ncdnew.value

select * from @NonContigData

What we're doing is building a temporary table "ncdnew" that uses the Row_Number function, and basing the update of your original table with the newvalue created in that temporary table.

Hope that helps.

answered Aug 10 '12 at 08:37 PM

ThomasRushton ♦
29.4k ● 6 ● 9 ● 36

Thanks for the quick reply, but what If my table as more column, should I create the @NonContigData table with all the column that's in my table or I can create a subset.

Same for the rows, If I have more rows+colums

should something like this work:

insert into @NonContigData values select * from table A

or

insert into @NonContigData values select * from table A where Name = 'ABC' if I want to do it one Name at a time

Once we update: @NonContigData

Do we insert the new value to the main table

Aug 10 '12 at 08:51 PM thimi3

That @NonContigData is the table I've used to store the data you provided above. Replace that with your table name.

If you only want to update a subset of your data, then yes, put a WHERE clause on your update statement.

If you have more columns, you only need to deal with enough to uniquely identify each row - the INNER JOIN's SELECT statement and the ON clause are what you need to look at.

Aug 10 '12 at 09:01 PM ThomasRushton ♦

Okay, I got mislead by that piece. Since I already have the table therefore I need to look at the update section of the script. Is it possible to have an output before updating, like a component that will output the missing int number

Aug 10 '12 at 09:06 PM thimi3

Can you also explained this section:

(ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Value))

Aug 10 '12 at 09:12 PM thimi3

What, you want me to show my working? ;-)

Before I wrote the UPDATE statement, I started with a SELECT statement along these lines:

declare @NonContigData table (Name char(3), Description varchar(max), value int)
insert into @NonContigData values 
('abc', 'day', 1), ('abc', 'night', 3), ('abc', 'week', 7),
('xyz', 'fff', 2), ('xyz', 'ggg', 3), ('xyz', 'vvv', 5),
('aaa', 'bbb', 1), ('aaa', 'ddd', 2), ('aaa', 'ccc', 3)

SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Value) AS RowNumberOrderedByValue,
(SELECT MIN(Value) FROM @NonContigData ncd2 WHERE ncd2.Name = ncd1.Name) AS MinValueForName,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Value) + (SELECT MIN(Value) FROM @NonContigData ncd2 WHERE ncd2.Name = ncd1.Name) - 1 AS NewValue
FROM @NonContigData ncd1

That shows all the data in the table, as well as the steps that go along to making the NewValue.

Aug 10 '12 at 09:15 PM ThomasRushton ♦

show 1 more reply

(comments are locked)

Finding non-contiguous number using SQL

Follow this question

Related Questions