Order issues

  • Hello!

    I am importing data from a source system running IBM iSeries. I use OPENQUERY to query the source system.

    The source system is a bit peculiar, so in table tblModelsAccount I have the following fields (my example is simplified)

    AccountNo ModelCode

    12345 A

    12345 B

    So which one is the current ModelCode for AccountNo 12345? Well, the not so obvious business rule is that the first one is always the current. That means A.

    So I import all rows to a table I have in my SQL Server DB. Like this

    INSERT INTO MYDATABASE.dbo.ModelAccounts(AccountNo, ModelCode)

    SELECT * FROM OPENQUERY(SourceDSN,'SELECT AccountNo, ModelCode FROM tblModelsAccount ')

    Then I do a Select from my import table

    SELECT * FROM MYDATABASE.dbo.ModelAccounts

    Interesting enough, the rows have shifted places.

    AccountNo ModelCode

    12345 B

    12345 A

    Obviously it is hard to use the rule "first one is the current", when this happens.

    But exactly what is deciding the order of rows in the SQL Server table when doing an insert like this?

    I would have expected the rows to be ordered like in the source system.

  • Clark,

    Welcome to the board. Please google for set theory. Basically, in all RDBMS a group of records (a set) has no inherent order: You cannot guarantee that the same set will always be returned in the same sequence unless you specify it; this may because of a database cleanup, an change to the query plan or any number of reasons. If you want records in a specific order you need to ask for them (the cororally of this is DON'T ASK FOR THE ORDER IF YOU DON'T NEED IT)

    If you want the record with the lowest suffix you need to do

    SELECT AccountNumber, MIN(AccountSuffix) FROM myTable GROUP BY AccountNumber

    You may then need to join this back to the source table again to access other fields.

    SELECT

    T1.*

    FROM

    myTable T1

    JOIN

    (

    SELECT

    AccountNo,

    MIN(ModelCode) AS 'LiveModelCode'

    FROM

    myTable

    GROUP BY

    AccountNo

    ) X on X.AccountNo = T1.AccountNor and X.LiveModelCode = T.ModelCode

    NOTE: There are lots of different way to do this sub-query. My preferred method is as a Common Table Expression but if you are new to T-SQL this may be confusing to you.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply