CODEWITHSUNDEEP
sql
Logesh
Logesh

Reputation: 9

order by string

Need to order id on based date & sequence number descending

ID is the combination of some concatenate string value BM- 6to8 digitCode- Date(DDMMYY)-SequenceNumber (01-99)

Create table #tempcheck (id varchar(50))
Insert into #tempcheck values('BM72220901021904')
Insert into #tempcheck values('BM72220901021906')
Insert into #tempcheck values('BM72220931011901')
Insert into #tempcheck values('BM72220931011903')
Select * from  #tempcheck order by id desc

id  
BM72220901021906  
BM72220901021904  
BM72220931011903  
BM72220931011901

Upvotes: 0

Views: 92

Answers (3)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Try This:

SELECT 
  *
from #tmpcheck
order by
  CONVERT(DATE, SUBSTRING(id, 9, 6)) AS date desc,
  CONVERT(int, RIGHT(id,2))  seq desc

It is stated in the question that the part before the date can be of variable length

SELECT 
  *
from #tmpcheck
order by
  CONVERT(DATE, SUBSTRING(RIGHT(id, 8), 1, 6)) AS date desc,
  CONVERT(int, RIGHT(id,2))  seq desc

Upvotes: 1

Morten
Morten

Reputation: 414

This solution should do it. It uses only string manipulation (no data type conversion) and should be rather fast.

SELECT *
FROM #tempcheck 
ORDER BY Left(Right(id, 4), 2) + Left(Right(id, 6), 2)
       + Left(Right(id, 8), 2) + Right(id, 2) DESC

It does the character counting from the back end of the string so that the variable length (6-8) code before the date does not matter.

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution:

SELECT CONVERT(DATE, SUBSTRING(id, LEN(id) - 5, 2) + '/' + SUBSTRING(id, LEN(id) - 7, 2) + '/' + SUBSTRING(id, LEN(id) - 3, 2), 1) AS datevalue, RIGHT(id, 2) AS seq 
FROM #tempcheck 
ORDER BY CONVERT(DATE, SUBSTRING(id, LEN(id) - 5, 2) + '/' + SUBSTRING(id, LEN(id) - 7, 2) + '/' + SUBSTRING(id, LEN(id) - 3, 2), 1) DESC, RIGHT(id, 2) DESC

demo on dbfiddle.uk

Upvotes: 1

Related Questions