Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

(Preface: In the past, this might have been a typical "Glenn will answer that" question...)

For SQL Anywhere, the docs clarify the following:

With ORDER BY, NULL sorts before all other values in ascending sort order.

(And consequently, NULL sorts after all other values in descending sort order, although that is not documented.)

The same seems to be true for MS SQL Server.

Question: Is this a standardized SQL behaviour or is this still implementation-specific - i.e. can I rely on that in general?

asked 07 Aug '13, 05:27

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 07 Aug '13, 05:28

OK, some web search seems to imply that this really is implementation-specific - to cite Wikipedia:

The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.

For example, ORACLE seems to behave differently (NULLS LAST if the sort is ASC, NULLS FIRST otherwise) but lets you control this by adding "NULLS LAST" / "NULLS FIRST" to the ORDER BY expression.

AFAIK, SQL Anywhere does not yet offer the NULLS FIRST / NULLS LAST option.

Resume: Don't expect too much standardization:)

permanent link

answered 07 Aug '13, 09:07

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%


And apparently, one can always use "ORDER BY ISNULL(..., myMagicValue)" to make NULLs appear in any desired position within other values...

(07 Aug '13, 09:11) Volker Barth
Replies hidden

You kids today, you don't know anything about magic!

That's not magic, THIS is magic... :)

SELECT A.row_num
  FROM RowGenerator AS A
 ORDER BY ( SELECT MOD ( B.row_num, 10 )
              FROM RowGenerator AS B
             WHERE B.row_num = A.row_num ) DESC;
(07 Aug '13, 11:39) Breck Carter

In case you want a DISORDERED TOP n clause and later SA versions do disturb you with their SQLCODE 122 warning ("The result returned is non-deterministic"), the following will help:

SELECT top 10 A.row_num
FROM RowGenerator AS A

Somewhat strang that a function that is non-deterministic by definition serves as a deterministic ordering:)

Note: That's no criticism at SQLCODE 122 at all, it has been of help to me often enough:)

(12 Aug '13, 07:21) Volker Barth

Actually... ORDER BY RAND() is a great idea for randomizing data. You don't have to generate a random number and then sort on that, just SELECT the item(s) you want and have them presented in random order. Sometimes it is a real PITA to have to include another column in the result set just to be able to do a sort; e.g., in a LIST() for example...

SELECT LIST ( A.row_num,
              ', ' 
              ORDER BY RAND() ) AS "1 to 10"
FROM ( SELECT TOP 10 row_num
         FROM RowGenerator 
        ORDER BY row_num ) AS A;

1 to 10
4, 1, 7, 3, 2, 8, 6, 9, 5, 10

1 to 10
5, 7, 4, 1, 9, 3, 8, 6, 10, 2
(12 Aug '13, 07:38) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 07 Aug '13, 05:27

question was seen: 5,917 times

last updated: 12 Aug '13, 07:38