x

Finding non-contiguous number using SQL

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

more ▼

asked Aug 10, 2012 at 08:17 PM in Default

thimi3 gravatar image

thimi3
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

@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!)
more ▼

answered Aug 13, 2012 at 06:19 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Nice. I'd forgotten that trick of updating a CTE...
Aug 13, 2012 at 08:15 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 10, 2012 at 08:37 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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, 2012 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, 2012 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, 2012 at 09:06 PM thimi3

Can you also explained this section:

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

Aug 10, 2012 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, 2012 at 09:15 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945

asked: Aug 10, 2012 at 08:17 PM

Seen: 906 times

Last Updated: Aug 13, 2012 at 08:15 AM