Why is this SQL query ordering incorrectly?

Question

I'm running the following query to "reorder" an updated table alphabetically. There's a column called nOrder that's supposed to be in sequential order. I'm updating that column as follows:

(edited: forgot to include AND @zone = zone in my question, but the same problem is occurring)

UPDATE Zones
SET nOrder = 0 WHERE stateKey = @key

DECLARE @zone INT
SET @zone = 1
DECLARE @i INT
SET @i = 1

WHILE @zone < 4
    BEGIN
        WHILE EXISTS (SELECT TOP 1 cDisplay FROM Zones WHERE stateKey = @key AND zone = @zone AND nOrder = 0)
            BEGIN
                UPDATE Zones
                SET nOrder = @i
                WHERE cDisplay = (SELECT TOP 1 cDisplay FROM Zones WHERE stateKey = @key AND zone = @zone AND nOrder = 0 ORDER BY cDisplay)
                SET @i += 1
            END
            SET @zone += 1
            set @i = 1
    END

The table then becomes:

FK zone                cDisplay                nOrder
                          .
                          .
                          .
                          .
10  3    MD: CAROLINE, DORCHESTER, KENT          40
10  3    MD: QUEEN ANNE'S                        41
10  3    MD: SOMERSET, TALBOT, WILCOMICO         42
10  3    ME: PORTLAND                            43
10  3    ME: YORK                                44
                          .
                          .
                          .
                          .
10  3    TX: COUNTIES NORTH OF HOUSTON           99
10  3    TX: DALLAS-FORT WORTH OUTER SUBURBAN   100
10  3    TX: EL PASO                            101
10  3    TX: MATAGORDA AND VICTORIA COUNTIES    102
10  3    TX: NORTHEAST COUNTIES                 103
10  3    TX: SAN ANTONIO OUTER SUBURBAN AREAS   104
10  3    TX: SOUTHERN TEXAS                      43   --???
10  3    TX: TYLER                              105
                          .
                          .
                          .
                          .

"FK" is actually "stateKey", but that doesn't fit.

why would this record be out of order? It doesn't make much sense to me that this would happen.

Does anyone have any ideas?

Answer 1

How about a much easier way to do this? I think you want:

WITH toupdate as (
      select z.*, row_number() over (partition by zone order by cDisplay) as seqnum
      from zones z
      where stateKey = @key AND nOrder = 0
     )
UPDATE toupdate
    set nOrder = seqnum;

The variable @zone is set but not used in your code (at least, it doesn't affect the update). I'm not sure what it should be doing -- but I suspect that is related to the problem with your code. In any case, using a CTE and ranking function is a better way to solve this problem.

Answer 2

You MUST use an ORDER BY clause any time you use "SELECT TOP..." otherwise you get an undefined ordering that can be anything the SQL Optimizer wants.