Need to order by Range Values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to order by Range Values?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2015-04-20 : 12:20:53

Hi Firends,

I have a field called CustomerRange which has values in the following way for some Customers:
Customer ID, CustomerRange
1111,[0-1]
1111,[1-2]
1111,[2-3]

And for some Customers
2222, 1
2222, 3
2222, 6

So bascially i want to write a select statement ordering the Customer range ..Its a text field in the database..How can i achieve this

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-20 : 12:29:05

if the single values are to be interpreted as being a range that is the start and end , i.e. 1 could be interpreted as [1-1] then you could use something like the following

;with MyCTE
AS
(

SELECT 1111 CustID,'[0-1]' TRange UNION
SELECT 1111,'[1-2]' UNION
SELECT 1111,'[2-3]' UNION
SELECT 2222, '1' UNION
SELECT 2222, '3' UNION
SELECT 2222, '6'
)

SELECT *, CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END SortedBy FROM MyCTE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2015-04-20 : 13:10:22

Thanks Michael but i ma looking if there is function or something i can use cause the values keep changing and somtimes could be 20 records.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-20 : 13:23:40

This was an example.

You would use this, but against your table

SELECT COLUMN FROM TABLE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END

but you could turn that could into a function.

Or have I misunderstood. Could you give me an example of what might change?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2015-04-20 : 15:48:04

Thank You Michae.It seems to work for the range values but the order by is not working when they are not ranges but simple integers as provided in my above example

2222, 1
2222, 3
2222, 6

They are not sorting in order.Thanks for keeping up with me..

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-21 : 13:06:27

It should work for both -

;with MyCTE
AS
(

--SELECT 1111 CustID,'[0-1]' TRange UNION
--SELECT 1111,'[1-2]' UNION
--SELECT 1111,'[2-3]' UNION
SELECT 2222 CustID, '1' TRange UNION
SELECT 2222, '3' UNION
SELECT 2222, '6'
)

SELECT *, CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END SortedBy FROM MyCTE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2015-04-21 : 15:08:22

;with MyCTE
AS
(

SELECT 2222 CustID,'11' TRange UNION
SELECT 2222, '3' UNION
SELECT 2222, '6'
)

SELECT *, CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END SortedBy FROM MyCTE
ORDER By CASE WHEN CHARINDEX('[',TRange) = 0 THEN '[' + TRange + '-' + TRange + ']' ELSE TRange END

Please check this example..This is not ordering correctly when the integer is greater 9 . Thank you

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-04-22 : 02:02:32

Can try this but not sure if the ordering will be as per your requirement:

SELECT *,
REPLACE(REPLACE(REPLACE(TRange,'[',''),']',''),'-','') AS SortedBy
FROM MyCTE
ORDER By CAST(REPLACE(REPLACE(REPLACE(TRange,'[',''),']',''),'-','') AS INT)

--------------------
Rock n Roll with SQL

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-22 : 07:21:34

Apologies: I coded with the data provided.

RocknPops solution will work -- revised

It actually will not work as [-1-2] is interpreted as 12

1111 [0-1] 01
2222 1 1
2222 3 3
2222 6 6
2222 11 11
1111 [1-2] 12
1111 [2-3] 23
2222 [78-99] 7899

I think I may even need a second sort - unless you don't care that [1-2] is before 1

1111 [0-1] 0
1111 [1-2] 1
2222 1 1
1111 [2-3] 2
2222 3 3
2222 6 6
2222 11 11
2222 [78-99] 78

;with MyCTE
AS
(
SELECT 1111 CustID,'[0-1]' TRange UNION
SELECT 1111,'[1-2]' UNION
SELECT 1111,'[2-3]' UNION
SELECT 2222, '1' UNION
SELECT 2222, '3' UNION
SELECT 2222, '6' UNION
SELECT 2222 CustID,'11' TRange UNION
SELECT 2222, '[78-99]' UNION
SELECT 2222, '6'
)

SELECT * ,CASE WHEN CHARINDEX('[',TRange) = 0 THEN TRange ELSE SUBSTRING(TRange,2,CHARINDEX('-',TRange,1)-2 ) END
FROM MyCTE
ORDER By CAST(CASE WHEN CHARINDEX('[',TRange) = 0 THEN TRange ELSE SUBSTRING(TRange,2,CHARINDEX('-',TRange,1)-2 ) END AS INT)

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2015-04-22 : 17:00:10

Thank you all for your help.

With the help google and online friend This is what i got.
But For another table the value of ranges do not have [] braces. they are just 0-1,1-3,7-8,3-6. In this scenario. How can i change the below code to work. Any help would be appreciated.

;WITH MyCTE
AS
(
SELECT 1111 CustID,CustomerRange
FROM (VALUES('[0-1]'),('[10-13]'),('[7-10]')) AS A(CustomerRange)

UNION ALL

SELECT 2222,CustomerRange
FROM (VALUES('11'),('3'),('14')) B(CustomerRange)
)

SELECT CustID,
CASE
WHEN CHARINDEX('[',CustomerRange) = 0
THEN CONCAT('[',CustomerRange,'-',CustomerRange,']')
ELSE CustomerRange
END AS CustomerRange, --Formatting it
order_col
FROM MyCTE
CROSS APPLY (SELECT CAST(CASE WHEN CHARINDEX('-',CustomerRange) > 0
THEN SUBSTRING(CustomerRange,2,CHARINDEX('-',CustomerRange) - 2)
ELSE CustomerRange
END AS INT)
) CA(order_col)
ORDER BY custID,order_col

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-04-23 : 06:46:10

Michael's solution would work, your last solution is the same. Just need to handle for 0-1,1-3...
here you go:

SELECT * ,
CAST(CASE WHEN CHARINDEX('-',TRange) > 0 AND CHARINDEX('[',TRange) > 0
THEN SUBSTRING(TRange,2,CHARINDEX('-',TRange)-2)
WHEN CHARINDEX('[',TRange) = 0 AND CHARINDEX('-',TRange) >0
THEN SUBSTRING(TRange,1,CHARINDEX('-',TRange)-1)
ELSE TRange
END
AS INT)
FROM MyCTE
ORDER By CustID,
CAST(CASE WHEN CHARINDEX('-',TRange) > 0 AND CHARINDEX('[',TRange) > 0
THEN SUBSTRING(TRange,2,CHARINDEX('-',TRange)-2)
WHEN CHARINDEX('[',TRange) = 0 AND CHARINDEX('-',TRange) >0
THEN SUBSTRING(TRange,1,CHARINDEX('-',TRange)-1)
ELSE TRange
END
AS INT)

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources