Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: MSSQL/Great Plains using DEX_ROW_ID to sort

    We are using MSSQL to manage different systems, we use the input from Great Plains and then we post some information to other systems.

    I started having some problems and we post a big invoice, the order was not correct. If I print the invoice the order was the correct one but if I open the MSSQL table, the order was not correct. After several test I found that if I sort using DEX_ROW_ID, I'll get the order order. Is this correct?

    What is the best way to sort the table in base of the time when the row was created?

    Thanks,
    Monica

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    After several test I found that if I sort using DEX_ROW_ID, I'll get the order order. Is this correct?
    Probably, but hard to tell without any further information.
    What is the best way to sort the table in base of the time when the row was created?
    ORDER BY CreationTimestampColumnName
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Well, I tried to use CreationTimeStamp but I get an error. My table does not have a timestamp column.

    My table has a column called RecordedTime and I have the date and time when the row was created; the problem is that the time is the same for a lot of rows.

    The DEX_ROW_ID is just a consecutive number. Do you see any problem if I use this field to order my query?

    Thanks,
    Monica

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If DEX_ROW_ID is an identity column, its values will be in the order of creation. In most cases it will be safe to use it in your ORDER BY clause.
    After several test I found that if I sort using DEX_ROW_ID, I'll get the order order.
    As you already stated, in your case it will work.

    A better design would keep an extra column like InvoiceLineNr (or some similar name), to store the sequence explicitly. Perhaps some column with that name is present?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    I don't have another identity column. I'm going to use DEX_ROW_ID and reply to the post if I find any problems.

    Thank you very much.
    Monica

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •