CODEWITHSUNDEEP
sql-serversql-server-2005
crimson
crimson

Reputation: 221

SQL Server dense_rank() on nvarchar column

From this question, is it possible to use dense_rank properly on an nvarchar column?

Here's a SQL fiddle where I created a nvarchar column, see the results yourself, and here's one where the column is int

Upvotes: 0

Views: 271

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Of course it is possible. But your examples are quite different. In the first, the values are ordered as:

1
11
2
3
4
5

In the second like this:

1
2
3
4
5
11

Hence, the results are different. Numbers stored as strings are treated as strings, not numbers.

EDIT:

There are two ways to "treat the nvarchar()" as a number. The first is to do a conversion, such as:

dense_rank() over (order by cast(Number as decimal)) grp

(or whatever type you want).

The second will work if the values are integers and do not have leading zeros:

dense_rank() over (order by len(Number), Number) grp

Upvotes: 2

Related Questions