Sybase Default ordering when using distinct clause

[#1]

Hello, We would like to know what is the default order sybase would use when using distinct in the select query. Here is the use case (Simple table with an int, decimal, string and date columns)

create table ##tab1 (intcol int, decicamlcol decimal(8,4), stringcol varchar(100), datecol date) go

Query 1: select intcol, datecol from ##tab1

Query 2: select distinct intcol , strcol , datecol, decimalcol from ##tab1

Query 1 - sorts the results first by the intcol and then on the datecol as given in the select clause.

However Query 2 - (which has a strcol in the selcet), orders the results in the reverse fashion starting with the decicamlcol, datecol, then on the strcol followed by the intcol. We did see this observation only when a string column is used in the select.

Can some please comment if there is a pattern sybase uses to order results when distinct is used and with out explicitly mentioning "order by" clause.

Thank you. Naresh

[Former Member]

The underlying assumption in the original question is that sorting is used for duplicate elimination.

While a SQL Anywhere server does support sort-based duplicate elimination, more often the optimizer will pick the (more efficient) hash-based duplicate-elimination strategy, which inherently does not generate a duplicate-free result set in a reliable order.

To guarantee the ordering of tuples in a result set, one MUST use an ORDER BY clause.