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

avatar 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

avatar image

WilliamD
26.2k 18 34 48

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

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

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.

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:

x2017

asked: Aug 10, 2012 at 08:17 PM

Seen: 1302 times

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

Copyright 2016 Redgate Software. Privacy Policy