Link to home
Start Free TrialLog in
Avatar of Gus Koutsivitis
Gus KoutsivitisFlag for United States of America

asked on

SQL Server Varchar column with numbers cannot be ordered with aggregate query.

Hi all,

I have an issue when trying to order a varchar column numerically from 1,2,3, etc...  It ends up being unordered no matter what I have tried.  I tried changing the column type to int, number, and BigInt, and still cannot order numerically.  I tried using the following with "Order By": ABS([DAY]), (0 + [DAY]) , CONVERT(), CAST() and keep getting the following error for each:  
"ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."


Below is a screen shot of the table results (you can see that the "DAY" column is unordered:
User generated image
My Query:

Select 
a.[DAY],
a.[ASSIST], 
a.[AMERICAN AMR], 
a.[EMS], 
a.[1ST RESPONSE],
a.[HATZOLAH OT],
a.[HATZOLAH BP],
a.[HATZOLAH CA],
a.[HATZOLAH FLATBUSH],
a.[HATZOLAH SG],
a.[HATZOLAH CH],
a.[HIP CENTRAL],
a.[HATZOLAH RO ],
a.[HATZOLAH SI ],
a.[HATZOLAH WI ],
a.[INSTACARE],
a.[KINGSBROOK],
a.[LUTHERAN],
a.[MAIMONIDES],
a.[METROPOLITAN],
a.[MIDWOOD AMBU],
a.[E# MIDWOOD V],
a.[NATION WIDE],
a.[NY PRESBY],
a.[RICHMOND CTY],
a.[SENIOR CARE],
a.[TRANSCARE],
a.[UNITED AMBUL],
a.[VICTORY],
a.[UNKNOWN  ???],

isnull(a.[ASSIST],0) + 
isnull(a.[AMERICAN AMR],0) +
isnull(a.[EMS],0) +
isnull(a.[1ST RESPONSE],0) +
isnull(a.[HATZOLAH OT],0) +
isnull(a.[HATZOLAH BP],0) +
isnull(a.[HATZOLAH CA],0) +
isnull(a.[HATZOLAH FLATBUSH],0) +
isnull(a.[HATZOLAH SG],0) +
isnull(a.[HATZOLAH CH],0) +
isnull(a.[HIP CENTRAL],0) +
isnull(a.[HATZOLAH RO ],0) +
isnull(a.[HATZOLAH SI ],0) +
isnull(a.[HATZOLAH WI ],0) +
isnull(a.[INSTACARE],0) +
isnull(a.[KINGSBROOK],0) +
isnull(a.[LUTHERAN],0) +
isnull(a.[MAIMONIDES],0) +
isnull(a.[METROPOLITAN],0) +
isnull(a.[MIDWOOD AMBU],0) +
isnull(a.[E# MIDWOOD V],0) +
isnull(a.[NATION WIDE],0) +
isnull(a.[NY PRESBY],0) +
isnull(a.[RICHMOND CTY],0) +
isnull(a.[SENIOR CARE],0) +
isnull(a.[TRANSCARE],0) +
isnull(a.[UNITED AMBUL],0) +
isnull(a.[VICTORY],0) +
isnull(a.[UNKNOWN  ???],0) 
Total 
from [AmbDrops].[dbo].[Drops] a

union all
Select 
'Total',
sum(isnull(a.[ASSIST],0)), 
sum(isnull(a.[AMERICAN AMR],0)), 
sum(isnull(a.[EMS],0)), 
sum(isnull(a.[1ST RESPONSE],0)),
sum(isnull(a.[HATZOLAH OT],0)),
sum(isnull(a.[HATZOLAH BP],0)),
sum(isnull(a.[HATZOLAH CA],0)),
sum(isnull(a.[HATZOLAH FLATBUSH],0)),
sum(isnull(a.[HATZOLAH SG],0)),
sum(isnull(a.[HATZOLAH CH],0)),
sum(isnull(a.[HIP CENTRAL],0)),
sum(isnull(a.[HATZOLAH RO ],0)),
sum(isnull(a.[HATZOLAH SI ],0)),
sum(isnull(a.[HATZOLAH WI ],0)),
sum(isnull(a.[INSTACARE],0)),
sum(isnull(a.[KINGSBROOK],0)),
sum(isnull(a.[LUTHERAN],0)),
sum(isnull(a.[MAIMONIDES],0)),
sum(isnull(a.[METROPOLITAN],0)),
sum(isnull(a.[MIDWOOD AMBU],0)),
sum(isnull(a.[E# MIDWOOD V],0)),
sum(isnull(a.[NATION WIDE],0)),
sum(isnull(a.[NY PRESBY],0)),
sum(isnull(a.[RICHMOND CTY],0)),
sum(isnull(a.[SENIOR CARE],0)),
sum(isnull(a.[TRANSCARE],0)),
sum(isnull(a.[UNITED AMBUL],0)),
sum(isnull(a.[VICTORY],0)),
sum(isnull(a.[UNKNOWN  ???],0)),


sum(isnull(a.[ASSIST],0) + 
(isnull(a.[AMERICAN AMR],0)) +  
(isnull(a.[EMS],0)) +  
(isnull(a.[1ST RESPONSE],0)) + 
(isnull(a.[HATZOLAH OT],0)) + 
(isnull(a.[HATZOLAH BP],0)) + 
(isnull(a.[HATZOLAH CA],0)) + 
(isnull(a.[HATZOLAH FLATBUSH],0)) + 
(isnull(a.[HATZOLAH SG],0)) + 
(isnull(a.[HATZOLAH CH],0)) + 
(isnull(a.[HIP CENTRAL],0)) + 
(isnull(a.[HATZOLAH RO ],0)) + 
(isnull(a.[HATZOLAH SI ],0)) + 
(isnull(a.[HATZOLAH WI ],0)) + 
(isnull(a.[INSTACARE],0)) + 
(isnull(a.[KINGSBROOK],0)) + 
(isnull(a.[LUTHERAN],0)) + 
(isnull(a.[MAIMONIDES],0)) + 
(isnull(a.[METROPOLITAN],0)) + 
(isnull(a.[MIDWOOD AMBU],0)) + 
(isnull(a.[E# MIDWOOD V],0)) + 
(isnull(a.[NATION WIDE],0)) + 
(isnull(a.[NY PRESBY],0)) + 
(isnull(a.[RICHMOND CTY],0)) + 
(isnull(a.[SENIOR CARE],0)) + 
(isnull(a.[TRANSCARE],0)) + 
(isnull(a.[UNITED AMBUL],0)) + 
(isnull(a.[VICTORY],0)) + 
(isnull(a.[UNKNOWN  ???],0))
)
from [AmbDrops].[dbo].[Drops] a

order by [DAY]

Open in new window


Any help would be appreciated.  Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OR creating an index field for the sorting, like:

;with yourTable as
(
    select '1' [Day] union all
    select '2' union all
    select '10'
), combine as
(
    Select [Day], cast([Day] as int) idx
    from yourTable
    union
    Select 'Total', 99999 idx
)
Select [Day]
from combine
order by idx

Open in new window

Avatar of Gus Koutsivitis

ASKER

Thank you, simple and it worked!