nirav nirav - 26 days ago 11
SQL Question

Order by and custom sorting in Microsoft SQL Server

I have a table with numeric and string values. I need to apply the custom sorting as mentioned below:-

CREATE TABLE [dbo].[TEST]
(
[Tag] [nvarchar](max) NULL,
[Category] [nvarchar](max) NULL,
[LE] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[Row_Id] [int] NOT NULL,

CONSTRAINT [PK_testsirius_TEST_0_Row_Id]
PRIMARY KEY CLUSTERED ([Row_Id] ASC)
)

Insert into TEST values (1,'Area','EMR','A',199)
Insert into TEST values (2,'Area','EMR','B',200)
Insert into TEST values (3,'Area','EMR','C',201)
Insert into TEST values (201,'Area','EMR','1',399)
Insert into TEST values (202,'Area','EMR','2',400)
Insert into TEST values (203,'Area','EMR','3',401)


Excepted output:

select *
from TEST
order by asc


Output:

1
2
3
A
B
C


Current output:

C
B
A
3
2
1


Requirement :


  • If the sort direction is [↑] then first sort all the numeric values from smallest to the largest, then sort all the time values from oldest to newest and then sort all the text values from A to Z

  • If the sort direction is [↓] then first sort all the text values from Z to A, then sort all the time values from newest to oldest and then sort all the numeric values from largest to the smallest

  • While sorting, always place the blank cells at the bottom.


Answer

You can use isNumeric().

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) ELSE 2147483647 END
,   Description

-- for a descending order you can use the maths idea that -1 * a number maintains magnitude but reverse the order ...

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) *     -1  ELSE -2147483648 END
,   Description desc

-- with the extra test case for 12345

CREATE TABLE [dbo].[TEST]
(
    [Tag] [nvarchar](max) NULL,
    [Category] [nvarchar](max) NULL,
    [LE] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [Row_Id] [int] NOT NULL,

    CONSTRAINT [PK_testsirius_TEST_0_Row_Id] 
       PRIMARY KEY CLUSTERED ([Row_Id] ASC)
)

Insert into TEST values (1,'Area','EMR','A',199)
Insert into TEST values (2,'Area','EMR','B',200)
Insert into TEST values (3,'Area','EMR','C',201)
Insert into TEST values (201,'Area','EMR','1',399)
Insert into TEST values (202,'Area','EMR','2',400)
Insert into TEST values (203,'Area','EMR','3',401)
Insert into TEST values (204,'Area','EMR','12345',402)

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) ELSE 2147483647 END
,   Description


-- example output

Tag Category    LE  Description Row_Id
201 Area    EMR 1   399
202 Area    EMR 2   400
203 Area    EMR 3   401
204 Area    EMR 12345   402
1   Area    EMR A   199
2   Area    EMR B   200
3   Area    EMR C   201

-- descending order

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) * -1  ELSE -2147483648 END
,   Description desc

-- example output

Tag Category    LE  Description Row_Id
3   Area    EMR C   201
2   Area    EMR B   200
1   Area    EMR A   199
204 Area    EMR 12345   402
203 Area    EMR 3   401
202 Area    EMR 2   400
201 Area    EMR 1   399
Comments