2

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.
Improve this question
8
  • What did you try already? Commented Nov 5, 2016 at 9:46
  • Your question is unclear!, please add more details ;). Commented Nov 5, 2016 at 9:47
  • In one table column description (varchar(max) column. I want the apply filter as mentioned below: - 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. Commented Nov 7, 2016 at 5:53
  • I have add the requirement in Question. please go through it. Commented Nov 7, 2016 at 5:56
  • Please show the output you want for descending order and clearly label that output as such. Commented Nov 7, 2016 at 5:56

3 Answers 3

Reset to default
4 
SELECT *
FROM TEST
ORDER BY CASE WHEN Description NOT LIKE '%[^0-9]%' THEN 0 ELSE 1 END,
         LEN(Description),
         Description
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

No. It's not working properly. when I insert the data 12564. it show on second line. like : 1,12564,2,3,4,A,B,C,D
@nirav If you wanted descending order, then why did you not ask this in your question?
I have add the requirement in Question. please go through it.
0

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 reverses 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
Improve this answer

2 Comments

HI, Please check for "select * from TEST order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) ELSE 2147483647 END , Description desc" not working.
I have add the requirement in Question. please go through it.
0

Have you tried this:

select * 
from TEST 
order by case when Description not like '%[0-9]%' then 1 else 0 end, Description
Improve this answer

2 Comments

No. It's not working properly. when I insert the data 12564. it show on second line. like : 1,12564,2,3,4,A,B,C,D
I have add the requirement in Question. please go through it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.