Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql Server query varchar data sort like int

I have one table like

CREATE TABLE table_name
(
P_Id int,
amount varchar(50)
)

Data Like

Id amount
----------
1 2340
2 4568
3 10000

Now I want to sort table by amount but one problem is amount is varchar so it sort table like this

Id amount
----------
3 10000
1 2340
2 4568

but i want result like this

Id amount
----------
3 10000
2 4568
1 2340

what should i do ?

like image 690
Archit Avatar asked May 30 '13 06:05

Archit


2 Answers

Cast amount column into Numeric in ORDER BY clause while selecting:

SELECT * FROM MyTable
ORDER BY CAST(amount AS Numeric(10,0)) DESC

Result:

Id amount
3 10000
2 4568
1 2340

See this SQLFiddle

like image 72
Himanshu Jansari Avatar answered Oct 11 '22 18:10

Himanshu Jansari


Try ABS():

SELECT * FROM MyTable ORDER BY ABS(MyCol) DESC;

SQL Fiddle

like image 20
aizaz Avatar answered Oct 11 '22 19:10

aizaz