The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

For SQL Anywhere, the docs clarify the following:

ORDER BY and NULL
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
29.3k287438644
accept rate: 32%

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
29.3k287438644
accept rate: 32%

1

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
2

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
1

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
ORDER BY RAND()

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
2

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×18
×13
×10
×5

question asked: 07 Aug '13, 05:27

question was seen: 1,865 times

last updated: 12 Aug '13, 07:38