CODEWITHSUNDEEP
phpmysqlsql
Pummy Manku
Pummy Manku

Reputation: 45

How to get numbers in sequence in sql?

I have this code:

$sql = "SELECT * FROM alexa WHERE alexapr BETWEEN 01 AND 20 ORDER BY alexapr ASC LIMIT 20";

Which give this result:

google.com 1
qq.com 10
msn.com 11
yahoo.co.jp 12
taobao.com 14
google.co.in 15
sina.com.cn 16
amazon.com 17
google.de 17
wordpress.com 18
linkedin.com 18
facebook.com 2
youtube.com 3
yahoo.com 4
blogspot.com 5
baidu.com 6
wikipedia.org 7
live.com 8
twitter.com 9

It's showing Alexa ranking of top sites, How to get number in sequence like 1,2,3,4,5 etc. Why it's showing 10,11,12 after 1? I want 1,2,3,4,5 and so on.

Upvotes: 0

Views: 166

Answers (3)

Robert
Robert

Reputation: 8767

This looks like your 'alexapr' column is a string, instead of an integer and that is why it is sorting in the method as displayed in the question.

Try: CAST(alexapr AS INT) or CONVERT(INT, alexapr)

Update: Since it appears that you are using MySQL, due to the limit clause, the above T-SQL will not work. Try: ALTER TABLE alexa CHANGE alexapr alexapr INT;

Upvotes: 1

Tarang
Tarang

Reputation: 656

It is treating alexapr column as character type, try to convert the values to number data type.

Upvotes: 1

yokoloko
yokoloko

Reputation: 2860

Change your alexapr column type to integer.

ALTER TABLE alexa CHANGE alexapr alexapr INTEGER IF you're on mysql

Upvotes: 4

Related Questions