IBM AS400 iSeries - sort order of select statement


Without an explicit ORDER BY clause the records could be returned in any order the SQL optimizer deems most efficient.

DB2/400 has the concept of "relative record number". New records are always appended to a table unless the "Reuse deleted records" setting on the table is *YES.

You can use the RRN function to order records by their physical ordering in the file:


Related videos on Youtube

Author by


Updated on December 02, 2022


  • Mike
    Mike about 1 month

    We have a vendor who manages an AS400 for our company. One of their services is providing files via a table that I can query using iSeries. However, the table has only one column, and each record is just a line from one of the files. So I need to select all of the records from the table while maintaining the order in which they were input, but I don't have a column I can use an ORDER BY on.

    I understand in relational databases, there is no guaranteed sort order on a select statement without an ORDER BY clause. Does the AS400 / iSeries system operate the same way? From the reading I have done, it appears to be a different sort of DBMS, but I'm not finding anything that tells me whether or not a select will potentially alter/ not alter the order in which the records were inserted into the table. It "appears" that whenever I run a select, I always get what appears to be the right sort order, but I want to guarantee it if I can.

    Thank you in advance for any advice and help.

    • Gordon Linoff
      Gordon Linoff almost 6 years
      If you are using SQL, what is the problem with including the ORDER BY, if only to be sure that you don't have unexpected errors in the future?
    • user2338816
      user2338816 almost 6 years
      Are all queries via SQL SELECT? If it's only a single-column table, is there a reason to "query" other than for a person simply to read the content?
  • CJ7
    CJ7 over 10 years
    Thanks for your answer. Regarding steps 4-9, can computer names be used instead of IP addresses?
  • Perdana Putra
    Perdana Putra over 10 years
    Yeah .. Welcome, I think it can't .
  • Mike
    Mike almost 6 years
    There's only 1 column, and the data in that column doesn't have any logical way to be sorted. So I have nothing to use ORDER BY with. It comes into our vendor as a text file, and our vendor inserts it into the table in the same order as the lines in the file, but on their own they don't have a number or anything to sort off of.
  • jmarkmurphy
    jmarkmurphy almost 6 years
    Down vote because of the "Consider adding an order by clause" stuff since the OP had previously explained why that is not a possibility in this case, both in the question and the comments.
  • danny117
    danny117 almost 6 years
    Some people blindly follow. Some don't.
  • Mike
    Mike almost 6 years
    This solved my issue. Thank you very much for your help!