problem in ORDER BY

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
anaspk
Forum Newbie
Posts: 7
Joined: Wed Mar 25, 2009 1:38 am

problem in ORDER BY

Post by anaspk »

hi i m Anas Raza..
i want to sort a record by a field let supose the field is registration number which is combination of alphabets and intergers,which is like this
b13
b14
b15
b11
b21
b1111
b1121
b123
b131
when i use ORDER BY thn they look like following:
b1111
b1121
b12
b123
b13
b131
b14
b15
b21
but i dont want sorting like this,i want to sort it in human way i mean like this
b11 (Which "ORDER BY" already ignor)
b12
b13
b14
b15
b21
b123
b131
b1111
b1121
plz help me to do that varchar field in order
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: problem in ORDER BY

Post by Bill H »

Well, by "human way" what you mean is a numeric sort. But the field is alphabetic. Humans can see digits and interpret them as both numbers and letters, but computers cannot do that. Numbers are stored as ones and zeroes only, and the digits that we can see as numbers it sees only as, guess what, alphabetic characters. As far as your database is concerned, there are no numbers here.

The only way to do what you want is to separate the alpha character from the numeric ones, right justify the numeric portion and pad it with spaces.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: problem in ORDER BY

Post by pickle »

Please look through the forums & make sure you post in the correct one.

Moving to Databases.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply