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 🙂

3 Comments

  1. 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. 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”.

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.