Order by numeric column issue

Question

Order by numeric column issue

as_1234

SSCrazy

Points: 2865
More actions
March 9, 2018 at 9:03 am

#327800

All,
I would appreciate some help in trying to identify why an order by isn't returning the results I thought it would
Test table and data creation:
CREATE TABLE [dbo].[tabusrItemCount](
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ItemCountItemRef] [uniqueidentifier] NULL,
[Year] [numeric](18, 0) NULL,
[Month] [numeric](18, 0) NULL,
[Week] [numeric](18, 0) NULL,
[Date] [numeric](18, 0) NULL,
[ItemCount] [numeric](18, 0) NULL,
[LastChangeTime] [datetime] NULL,
[LastChangedBy] [uniqueidentifier] NULL,
CONSTRAINT [PK_tabusrItemCount] PRIMARY KEY CLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabusrItemCount] ADD CONSTRAINT [DF_tabusrItemCount_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
insert into tabusrItemCount(year,month,date,ItemCount) values (2018,1,1,50)
insert into tabusrItemCount(year,month,date,ItemCount) values (2010,1,1,50)
SQL statement:
select year from tabusritemcount order by year,month, date desc
The results put the 2010 year at the top. I was expecting 2018 first. It's a numeric column so it shouldn't be doing any kind of alphanumeric sorting?
If I run:
select year from tabusritemcount order by year desc
Then 2018 is first.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

as1981 - Friday, March 9, 2018 9:03 AM
All,
I would appreciate some help in trying to identify why an order by isn't returning the results I thought it would
Test table and data creation:
CREATE TABLE [dbo].[tabusrItemCount](
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ItemCountItemRef] [uniqueidentifier] NULL,
[Year] [numeric](18, 0) NULL,
[Month] [numeric](18, 0) NULL,
[Week] [numeric](18, 0) NULL,
[Date] [numeric](18, 0) NULL,
[ItemCount] [numeric](18, 0) NULL,
[LastChangeTime] [datetime] NULL,
[LastChangedBy] [uniqueidentifier] NULL,
CONSTRAINT [PK_tabusrItemCount] PRIMARY KEY CLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabusrItemCount] ADD CONSTRAINT [DF_tabusrItemCount_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
insert into tabusrItemCount(year,month,date,ItemCount) values (2018,1,1,50)
insert into tabusrItemCount(year,month,date,ItemCount) values (2010,1,1,50)
SQL statement:
select year from tabusritemcount order by year,month, date desc
The results put the 2010 year at the top. I was expecting 2018 first. It's a numeric column so it shouldn't be doing any kind of alphanumeric sorting?
If I run:
select year from tabusritemcount order by year desc
Then 2018 is first.
Thanks

Simple, in the following statement you are only sorting in descending order the date column. The first two columns are still being sorted in ascending order. You need to specify the sort order (descending or ascending) for each column in the order by clause.
select year from tabusritemcount order by year,month, date desc

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

The DESC in the ORDER BY clause only applies to the column it immediately follows. Try this:
ORDER BY year DESC, month, date

John

as_1234 SSCrazy Points: 2865 More actions · Answer 3

All,

Thanks for your help. Sorry for missing something so basic. I generally use ascending so I've never noticed my error before.

Thanks