Php:Problem in sorting of elements?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
esandra
Forum Newbie
Posts: 24
Joined: Sun Aug 30, 2009 11:11 pm

Php:Problem in sorting of elements?

Post by esandra »

Hi. I am having problems arranging some elements.
This is what happens when we do an 'order by field_name asc':

0302MZ2010
0401ML2010
0505ML2010
0610ML2009
0615MZ2010

This is what I want to happen:

0302MZ2010
0401ML2010
0505ML2010
0615MZ2010
0610ML2009

Arrange the year in desc order and the rest should be in asc order.
Now, I'm very confused and I don't know how to do this.
Thanks a lot and have a nice day.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Php:Problem in sorting of elements?

Post by John Cartwright »

Perhaps you can do,

[text]ORDER BY substr(field_name, -4) DESC, field_name ASC[/text]

However, this is terribly inefficient and won't make use of any index. Your best bet is to parse the model number when you are inserting the data, and use a seperate year column. This way, you can make proper use of indexes and can organize your data clearly.
esandra
Forum Newbie
Posts: 24
Joined: Sun Aug 30, 2009 11:11 pm

Re: Php:Problem in sorting of elements?

Post by esandra »

thank you it worked for me
Post Reply