1

I have data which has 5000 rows with 40 columns. I want to sort the data according to the first column in the row, which contains the id value of the particular row. The values in the id column are like 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,.....100,101,102.....1000,1001..... when I use a SQL statement with the order clause ORDER by id ASC.

Data is sorting in this manner..1,10,100,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,101.102,103,........

But I want the data to be in ascending order like: 1,2,3,4,5,6,7,8,9,10,11,12,13.....

This is the statement: $result = mysql_query("SELECT * FROM masterdb ORDER BY id ASC");

Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
sasi
  • 51
  • 6

1 Answers1

2

You may want to consider making your id column a numeric type if you expect to have the need to often sort numerically. That being said, one workaround would be to cast the id column to a numeric type and then sort using this:

SELECT *
FROM masterdb
ORDER BY CAST(id AS UNSIGNED)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I want to keep my id coumn as varchar , because sometimes my id contains alpabets also like TNDIDTNC120134,TNDIDTNC120135,TNDIDTNC120136....along with the numeric id's like 1,2,3,4,5,6...In this situation ,how can i sort all the alphanumerics in the starting and the numerics at the end. – sasi Mar 01 '17 at 06:06
  • @sasi Does the `id` column _always_ have the format of letters followed by numbers? Furthermore, is the _width_ of the letter portion always the same? Update your question showing the full extent of what this column might look like. – Tim Biegeleisen Mar 01 '17 at 06:10
  • Sometimes id is characters and numericals and sometimes only numericals. The width of the id is always of this length ITNMWROCT160010 ,but sometimes its lesser than this with just numerical values like 1,2,3,4,5,6,7,8,9,10,......100,101,102,103....1000 – sasi Mar 01 '17 at 06:50
  • If the width of the id is always some length, but sometimes less, then it isn't always some length. Update your question and _clearly_ show us the range of values which the `id` column can take. – Tim Biegeleisen Mar 01 '17 at 06:54
  • The range of the value is 15 and if id is an alphanumeric like 'ITNMWROCT160010' it is fixed length for all alphanumeric entries,if it is only numeric like 1,2,3,4,5,6,7,8.... – sasi Mar 01 '17 at 07:12
  • Please update your question showing us what the range is. I won't comment further until this has been done. – Tim Biegeleisen Mar 01 '17 at 07:15