Order MySQL Table by Ascending Order (Numbers)

Question

i want to arrange est_dilt_pple table in ascending order.

 pub_name est_dily_pple Jex Max Publication 11530 BPP Publication Mumbai 123500 New Harrold Publication 12563 Ultra Press Inc. 9500 Mountain Publication 9300 Summer Night Publication 53698 Pieterson Grp. of Publishers 50000

i tried this

SELECT * FROM publisher ORDER BY est_dily_pple ASC;

Result

pub_name est_dily_pple Jex Max Publication 11530 BPP Publication Mumbai 123500 New Harrold Publication 12563 Pieterson Grp. of Publishers 50000 Summer Night Publication 53698 Mountain Publication 9300 Ultra Press Inc. 9500

PROBLEM

But it is not ascending. "123500" is more than all, it should showed at bottom. any help me in this please!

I am sure est_dily_pple is char or varchar as it is getting sorted in terms of string change its type to int — learner
– learner, Commented Jun 6, 2013 at 9:55

duffymo · Accepted Answer · 2013-06-06 09:54:28Z

4

Looks like the column is a text or varchar type, with string sorting semantics.

Make it an integer or number type and you'll fare better.

answered Jun 6, 2013 at 9:54

duffymo

310k46 gold badges376 silver badges571 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2336315 Over a year ago

@user2457175 Really ? Show us that.

duffymo Over a year ago

I think not....if it were, the sorting would be correct AND you wouldn't have that odd rendering for the one value that's a problem. Show the DDL for the table.

AllTooSir · Accepted Answer · 2013-06-06 09:56:00Z

0

The best solution is to make the datatype of column est_dily_pple numeric. Or else you can try this :

SELECT * FROM publisher ORDER BY CAST(est_dily_pple as SIGNED INTEGER) ASC

answered Jun 6, 2013 at 9:56

AllTooSir

49.5k17 gold badges132 silver badges164 bronze badges

3 Comments

user2336315 Over a year ago

Yes it would work, but I think that the error there is that the schema of the database is clearly wrong !

duffymo Over a year ago

This will work, but it'll mean a TABLE SCAN for every SELECT. You'll have to do it every row.

AllTooSir Over a year ago

I mentioned this as a secondary solution .

Collectives™ on Stack Overflow

Order MySQL Table by Ascending Order (Numbers)

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related