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 results should be free to change order unless explicitly directed otherwise.

As Volker mentioned, the order may depend on what is optimal for that database server. Because of this, different plans (index/table scan, nested join/hash join, etc.) will cause evaluation of the query in different orders and can easily change the order of the output.

If you want an ordering you can rely on, you should specify this explicitly.