Sort Rules

When two rows have equivalent values in the SAP ASE sort order, rules are used to order the rows.

  • The values in the columns named in the order by clause are compared.

  • If two rows have equivalent column values, the binary value of the entire rows is compared byte by byte. This comparison is performed on the row in the order in which the columns are stored internally, not the order of the columns as they are named in the query or in the original create table clause. In brief, data is stored with all the fixed-length columns, in order, followed by all the variable-length columns, in order.

  • If rows are equal, row IDs are compared.

    Given this table:
    create table sortdemo (lname varchar (20),
                           init char (1) not null)
    And this data:
    lname      init 
    ---------- ---- 
    Smith      B 
    SMITH      C
    smith      A 
    You get these results when you order by lname:
    lname      init 
    ---------- ---- 
    smith      A 
    Smith      B 
    SMITH      C

    Since the fixed-length char data (the init column) is stored first internally, the order by sorts these rows based on the binary values “Asmith”, “BSmith,” and “CSMITH”.

    However, if the init is of type varchar, the lname column is stored first, and then the init column. The comparison takes place on the binary values “SMITHC”, “SmithB”, and “smithA”, and the rows are returned in that order.