Clever Geek Handbook

Sorting in ascending order in a SQL query


I have a varchar field CaseNo

in a table that contains data in this format eg.

FA/12/2014,
FA/11/2015, 
FA/12/2015, 
FA/11/2014,  
CC/12/2015,
CC/11/2015

I wanted to sort the query result select

as follows

CC/11/2015
CC/12/2015
FA/11/2014
FA/12/2014
FA/11/2015
FA/12/2015

First, it must sort the first two characters alphabetically. And then the remaining numbers in ascending order and the result should be similar as above.
Note: - the last part of the data is the year, but the middle part is not the month, it is just a number.
Is it possible to do this.
Thanks to

+3


source to share


4 answers


You have to do this:

    select * from temp
    order by 
    left(columnA,2) asc ,
    right(columnA,4) asc,      
    cast(replace(replace(columnA,left(columnA,3),''),right(columnA,5),'') as int) asc


And DEMO is here! So you've edited the post and it should work fine. This is not a conversion to Date of the last part of the string

+2


source


This is probably what you are looking for:

select * from table1 
order by left(col1, 2), convert(date, '01/' + substring(col1, 4, 7), 103)


My guess was that the last part is month + year, but of course it could be something else.

+3


source


Try it order by

. More general way

ORDER  BY LEFT ('FA/12/2014', Charindex('/', 'FA/12/2014') - 1),
          Cast('01'+ Substring('FA/12/2014', Charindex('/', 'FA/12/2014'), 8) AS DATE)

DEMO

SELECT *
FROM   (VALUES ('CC/11/2015'),
               ('CC/12/2015'),
               ('FA/11/2014'),
               ('FA/12/2014'),
               ('FA/11/2015'),
               ('FA/12/2015')) tc (dates)
ORDER  BY LEFT (dates, Charindex('/', dates) - 1),
          Cast('01'
               + Substring(dates, Charindex('/', dates), 8) AS DATE)

Result:

dates
----------
CC/11/2015
CC/12/2015
FA/11/2014
FA/12/2014
FA/11/2015
FA/12/2015
+1


source


I think you can use PARSENAME in this case

SELECT *
FROM   #Your_Table
ORDER  BY Parsename(Replace(columnA, '/', '.'), 3),
          Parsename(Replace(columnA, '/', '.'), 1),
          Parsename(Replace(columnA, '/', '.'), 2)
0


source






More articles:


All Articles