Sorting of a Code field in SQL vs. Native

Did you know that a Code field is sorted different depending of you are using Native or SQL?

SQL will sort the Code field as a Varchar – which means it will sort the Code field as text!

In the following sorting example you can see the sorting Native vs. SQL

Native SQL
1 1
2 10
3 2
10 3
A A
AA B
B C
C AA

So what to do if you are using SQL and still want a code field to be sorted in the same way as in Native? Use temporary tables!

Temporary tables acts in the same way as a Native table :-)

You can leave a response, or trackback from your own site.

3 Responses to “Sorting of a Code field in SQL vs. Native”

  1. Tim81Bln says:

    There is a very simple way to get even real sql tables sorted like native tables.

    Change the property “SQL Data Type” for the code field from “undefined” to “Variant”. Variant-Fields are sorted the same way as native code fields.

  2. iby says:

    Setting the “SQL Data Type” will not let the code field be sorted in the same way as in native code fields!

    The fields will be sorted as following:

    Native SQL SQL – Variant Datatype
    1 1 A
    2 10 B
    3 2 C
    10 3 AA
    A A 1
    AA B 2
    B C 3
    C AA 10

    If you only look on the numbers, then yes they will be sorted in the same way. But if you are looking on the letters, or are using a mixed environment, then they are not sorted in the same way by just using “Variant”.

  3. Apria says:

    Good words.

Leave a Reply


six + = 13