ORDER BY problem

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
Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

ORDER BY problem

Post by Foxy999 »

When I use the query:

SELECT * FROM ELEC ORDER BY id DESC

And my id in table:

10, 11, 12, 13, 3, 5, 6, 7, 8, 9

But the result is not correct (or I am doing something wrong):

9, 8, 7, 6, 5, 3, 13, 12, 11, 10

How can I make it output:

13 . . . 3

Please help,

Foxy
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: ORDER BY problem

Post by VladSun »

Please, post your table structure (i.e. DESCRIBE TABLE my_table),
post your exact query,
post the exact result produced by this query.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: ORDER BY problem

Post by califdon »

Your id field is obviously ASCII text, not a number. Usually not a good idea for an ID field. In any case, it is doing exactly what you asked it to do, sort descending. Since it is ASCII text, the highest entry is "9", then "8", and so on until it gets to a text beginning with "1", the highest being "13". That's how ASCII text is sorted, descending. If you expect it to be the same as if they were numbers you could convert the id field to integers, but that would give you problems if you ever have any non-numeric values in your field.
Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

Re: ORDER BY problem

Post by Foxy999 »

califdon wrote:Your id field is obviously ASCII text, not a number. Usually not a good idea for an ID field. In any case, it is doing exactly what you asked it to do, sort descending. Since it is ASCII text, the highest entry is "9", then "8", and so on until it gets to a text beginning with "1", the highest being "13". That's how ASCII text is sorted, descending. If you expect it to be the same as if they were numbers you could convert the id field to integers, but that would give you problems if you ever have any non-numeric values in your field.
Thanks, that's what I thought I had to do
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: ORDER BY problem

Post by VladSun »

califdon wrote:Your id field is obviously ASCII text, not a number.
Nice catch :) I feel a little bit stupid now :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: ORDER BY problem

Post by superdezign »

VladSun wrote:Nice catch :) I feel a little bit stupid now :)
You'll notice it the next time, though. :3
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: ORDER BY problem

Post by califdon »

Don't feel bad, Vlad, we all need to feel stupid now and then, it's good for the soul! If I don't feel stupid at least a few times a week, I try to do something so I will! :wink:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: ORDER BY problem

Post by VladSun »

superdezign wrote:
VladSun wrote:Nice catch :) I feel a little bit stupid now :)
You'll notice it the next time, though. :3
Well... obviously (for me) I prefer the "linear iteration" model of debugging someone's else issues - i.e. the KISS principle applied.
From my experience, it works better than "supposing" more "complicated" issues.

But in this particular case I really, really should have noticed the "issue pattern" :) That's why I feel stupid :) It wasn't a "complicated" issue.

And yes - next time I won't miss it, ha-ha-ha :)
Last edited by VladSun on Mon Jul 27, 2009 5:37 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: ORDER BY problem

Post by VladSun »

califdon wrote:Don't feel bad, Vlad, we all need to feel stupid now and then, it's good for the soul! If I don't feel stupid at least a few times a week, I try to do something so I will! :wink:
I second that :) This forum teaches much more about life (and related) skills than PHP ones ;) Thanks for that!
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply