Sort in sql server

John

10y921Visual Basic .NET

how can i sort this no.

Web\372\15-16

Web\45\16-17

Web\01\15-16

Web\371\15-16

according to this

Web\45\16-17

Web\372\15-16

Web\371\15-16

Web\01\15-16

(In this 16-17 defines the year)

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp10y ago
Accepted answer
Hi,
You can use below query for above case
SELECT * FROM yourtable ORDER BY substring(yourcolname,len(yourcolname)-4,len(yourcolname)) DESC, cast(substring(substring(yourcolname,CHARINDEX('\',yourcolname)+1,len(yourcolname)),0,CHARINDEX('\',substring(yourcolname,CHARINDEX('\',yourcolname)+1,len(yourcolname)))) as decimal) desc
0
John10y ago
Thanks i solved it by converting it to int
0
Midhun Tp10y ago
Hi,
Is the format of data in that column is similar to the ones you provided?
That is Web\{some number}\{year}
0
Nitin Sontakke10y ago
It helps, if you share the code as well. Furthermore, as you are taking up the approach provided by Mithun, I suggest you include the columns created in ORDER BY clause in SELECT first, so that you know what the functions are returning.
0
John10y ago
Hi ,Midhun T P
sorry but i dnt know why i m getting the error
Error converting data type nvarchar to numeric.
0
Nitin Sontakke10y ago
This is where normalization comes in picture. Each information element must be stored separately and should be combined as per the business need at the last moment.
In this case voucher number and financial year should have been separately and should have been combined in a select statement (probably in a view).
To avoid pain, further maintenance and performance issues later, i suggest you should still be able to do it.
0
Vinay Singh10y ago
Hi use sub string and then use those value to short
SELECT MID(column_name,start,length) AS some_name FROM table_name
and now use this table value to short in other query

For detail

http://stackoverflow.com/questions/9758008/sql-server-select-query-ordering-by-substring
http://stackoverflow.com/questions/8187828/sql-order-by-using-a-substring-within-a-specific-column-possible
0