Tag: T-SQL

It seems that every month I have someone asking the question about ordering or row numbers for a query. Let’s get one thing clear from the start: there are no "row numbers" in a table.

You can assume that the first row you inserted is row number one, but it’s not. In fact, depending on the indexing or lack of indexing, you may or may not get that row returned first by a query. You can add an ORDER BY when you query the table, and in that case you can get the rows returned in a certain order every time, however the row number is not linked to a row.

As an example. If I have this People table:

ID Name
-- -------
1  Steve
2  Gail

and I query:

select ID, name from people order by name

I get

ID Name
-- -------
2  Gail
1  Steve

I could add a row number

 
SELECT row_number() OVER (ORDER BY [name])
       , [Name]
   FROM dbo.People

and get this:

   Name
-- -------
1  Gail
2  Steve

But "Gail" isn’t linked to "1" as a row number. If I do this:

 
INSERT people SELECT 3, 'Bob'

SELECT row_number() OVER (ORDER BY [name])
       , [Name]
   FROM dbo.People

I now get this:

   Name
-- -------
1  Bob
2  Gail
2  Steve

Now "Bob" is 1. You can get row numbers, but they are only linked to an ORDER BY and a specific result set. If the data changes, the row numbers may move.

While it might appear in some queries that you are getting consistent ordering of results, don’t confuse coincidence with causality. You might live on those assumptions for years, building code on them, and then make a few changes and lots of things break.

If you need ordering, use ORDER BY.

When I was in college, and even high school, all of my computer science classes required me to build algorithms. Often they were simple things, like implement a sort, or reverse a string, or shuffle a deck of cards. Those seemingly silly and trivial exercises, however, build the skills of pattern recognition and implementation in computer science. Sometimes I think we don’t do enough of that for people that are tackling computer careers these days.

I saw a post from someone that had an incrementing column, an identity, that impacted another field. Basically whenever the first column reached “10”, you wanted to add one to the second column.

Easy, right? I think so, and to show someone how they might create an update statement, or even see the pattern, I built a quick tally table.

SELECT Top 205 IDENTITY(INT,1,1) as N
  INTO Tally 
  FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2

From there, I then looked at the pattern. Every 10 items, I need to add one. That’s a pattern, and the way that pattern is easily discerned in math is with a modulo operation. To the rest of the world, that’s a remainder. If you look at the pattern of remainders of an increment divided by 10, it’s this:

n           modulo

———– ———–

1           1

2           2

3           3

4           4

5           5

6           6

7           7

8           8

9           9

10          0

11          1

12          2

13          3

14          4

15          5

16          6

17          7

18          8

19          9

20          0

21          1

22          2

from this code:

SELECT Top 205 IDENTITY(INT,1,1) as N
  INTO Tally 
  FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2
  

SELECT n
  , n % 10
 FROM Tally  
  
DROP TABLE tally

That mans that we can see each time there is a zero remainder, we want to perform an increment. So essentially if you detect an update, do a modulo, and get a zero, then you update the next column.

It gets a little more complicated if there can be multiple rows updated or added at once, but here is the overall code that essentially builds a table of numbers that increment for each 10 on the previous value.

SELECT Top 205 IDENTITY(INT,1,1) as N
  INTO Tally 
  FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2
  
DECLARE @b INT

SELECT @b = 1

SELECT n
  , n % 10
  , @b
  , n
  , @b + (1 * (n / 10)) 'col b'
  , CASE WHEN (n % 10) = 0 THEN 'add 1' ELSE '' END 
 FROM Tally  
  
DROP TABLE tally

You end up with this:

n                                   n           col b      
———– ———– ———– ———– ———– —–

1           1           1           1           1          
2           2           1           2           1          
3           3           1           3           1          
4           4           1           4           1          
5           5           1           5           1          
6           6           1           6           1          
7           7           1           7           1          
8           8           1           8           1          
9           9           1           9           1          
10          0           1           10          2           add 1

11          1           1           11          2          
12          2           1           12          2          
13          3           1           13          2          
14          4           1           14          2          
15          5           1           15          2          
16          6           1           16          2          
17          7           1           17          2          
18          8           1           18          2          
19          9           1           19          2          
20          0           1           20          3           add 1

21          1           1           21          3          
22          2           1           22          3          
23          3           1           23          3      

If I had started at zero, you’d see a more traditional increment of 0 for column b to start with.