Why the record is not ordered as expected?

Question

I am trying to create an script and I need to iterate the rows of a table, but I want to order by type and later by ID. I am using this code:

declare @MyTempTable table
(
    MyId bigint
);

INSERT INTO @MyTempTable 
 (MyId)
        SELECT MyId
        FROM MyTable
        where MyForeignKey = 8
        order by MyIdType, MyId;

while exists(select 1 from @MyTempTable)
begin
    select top 1 @myFirstRow = x.MyId from @MyTempTable x;

    --Do something
end

The problem is that when I get the first row of the temp table, I don't get the expected row, I get the row with the lowest ID, but it is of a type with higher type, so it seems that when I populate the temp table the order condition is not took in account.

So I would like to know how I could iterate the result in the order that I need, first order by type to group the rows by type and later order by ID in each group.

Tables are inherently unsorted. When you query a table without an `ORDER BY` clause, there is no guarantee about the order in which rows are returned. So your `top 1` query needs an `ORDER BY`. — HoneyBadger, Sep 04 '19 at 08:28
...and the `ORDER BY` in your `INSERT INTO` query is entirely redundant; it does nothing to define the order in the table, because there is no such concept. — Richard Hansell, Sep 04 '19 at 08:30
@RichardHansell Unless there's an identity column in the table, in which case the `order by` clause in the `insert...select` determines the order of the `identity` column. [See here.](https://stackoverflow.com/a/53102550/3094533) — Zohar Peled, Sep 04 '19 at 11:12

score 3 · Accepted Answer · answered Sep 04 '19 at 08:33

Something like this might work better:

declare @MyTempTable table
(
    MyId bigint,
    order_id INT
);

INSERT INTO @MyTempTable 
 (MyId, 
 order_id)
        SELECT MyId,
        ROW_NUMBER() OVER (ORDER BY MyIdType, MyId) AS order_id
        FROM MyTable
        where MyForeignKey = 8;

while exists(select 1 from @MyTempTable)
begin
    DECLARE @order_id INT;
    select top 1 @myFirstRow = x.MyId, @order_id = x.order_id from @MyTempTable x ORDER BY order_id;

    --Do something
    DELETE FROM @MyTempTable WHERE order_id = @order_id;
end;

Now we store the order in the temporary table, so we can use it to pull the rows back in the right order, and it also helps when we have finished with a row, and want to delete it.

Why the record is not ordered as expected?

1 Answers1