SQL Order By not working properly

问题:

I have a table like this

 CREATE TABLE [dbo].[tbl_LandRigs](
    [ID] [int] IDENTITY(700000,1) NOT NULL,
    [Company] [nvarchar](500) NULL,
    [Rig] [nvarchar](500) NULL,
    [RigType] [nvarchar](200) NULL,
    [DrawWorks] [nvarchar](500) NULL,
    [TopDrive] [nvarchar](200) NULL,
    [RotaryTable] [nvarchar](500) NULL,
    [MudPump] [nvarchar](500) NULL,
    [MaxDD] [nvarchar](50) NULL,
    [Operator] [nvarchar](500) NULL,
    [Country] [nvarchar](200) NULL,
    [Location] [nvarchar](500) NULL,
    [OPStatus] [nvarchar](200) NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [CreatedByName] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_LandRigs] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And I am trying to get data from MaxDD column in Descending order

SELECT  distinct "MaxDD" FROM [tbl_LandRigs] ORDER BY "MaxDD" Desc

But this returns data in following order According to my calculation 4000 must be the first value followed by others.But this results astonished me.Can any one help me out in this?

回答1:

You are storing them as text(nvarchar), that's why you get lexographical order. That means every character is compared with each other from left to right. Hence 4000 is "higher" than 30000 (the last zero doesn't matter since the first 4 is already higher than the 3).

So the correct way is to store it as a numeric value. However, that seems to be impossible since you also use values like 16.000 with 4.1/2"DP. Then i would add another column, one for the numeric value you want to order by and the other for the textual representation.

回答2:

As MaxDD is a varchar, not a number it is sorted in lexicographical order (i.e. ordered by the first character, then second, ...), not numerical. You should convert it to a numerical value

回答3:

This behaviour is due to the nvarchar type.

Try this:

SELECT  distinct "MaxDD" FROM [tbl_LandRigs] ORDER BY CAST ("MaxDD" as Int)