Reputation: 4811

Sort Order in SQL Server

Can I change the default sort order in a SQL server database so that nulls and zero length strings are displayed last.

Its SQL Server 2000

I stress, I want to change the default order for all queries, if possible

Upvotes: 2

Answers (3)

A-K

Reputation: 17090

No you cannot do that: Without ORDER BY, there is no default sort order. This is a very common question, so I wrote a canned answer: Without ORDER BY, there is no default sort order

Upvotes: 4

Andomar

Reputation: 238296

You can do almost any sort using a case in an order by. Here's the null columns first, then the empty strings, and the rest ordered on col1 and col3:

select  *
from    YourTable
order by
        case when col1 is null then 1
             when col1 = '' then 2
             else 3
        end
,       col2
,       col3 desc

Upvotes: 5

BarsMonster

Reputation: 6583

add a dummy newcolumn = (length(targetcolumn)>0), and sort by this first.

Upvotes: 1

Sort Order in SQL Server

Answers (3)

Related Questions