mySQL Order by not working... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

mySQL Order by not working...

Author

Topic

jhermiz

3564 Posts

Posted - 2005-02-08 : 10:54:44

Consider data such as this:


Price     Year
---------------
99,999    1990
100,000   1990
500       1990
1         1990
200       1991
21000     1991
999,999   1990

Well when i run a query such as

SELECT price, year FROM myTable ORDER BY Year ASC, PRICE DESC

I get unexpected results where it thinks
99,999 > 100,000

Is it only looking at the first number or something ? Do I need to convert this ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-08 : 11:50:47

Must be a non-numeric datatype in that column. Why would a column called Price not be a numeric datatype?

You should be familiar by now with the difference between sorting text and sorting numerics, right?

- Jeff

jhermiz

3564 Posts

Posted - 2005-02-08 : 11:53:53

quote:
Originally posted by jsmith8858

Must be a non-numeric datatype in that column. Why would a column called Price not be a numeric datatype?

You should be familiar by now with the difference between sorting text and sorting numerics, right?

- Jeff

Yes I am, maybe it is numeric, have to ask my buddy, its not my database.

Did see that it worked in sql server fine was wondering the same thing...

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-08 : 11:59:42

again, it depends on the datatype of that column.

if it is not a numeric column then it is "working" fine. That's how text is SUPPOSED to sort. just because the text contains numeric characters doesn't mean that the computer should figure out "oh, i bet he wants this to be sorted like numbers!" ... right ?

- Jeff

jhermiz

3564 Posts

Posted - 2005-02-08 : 13:05:33

quote:
Originally posted by jsmith8858

again, it depends on the datatype of that column.

if it is not a numeric column then it is "working" fine. That's how text is SUPPOSED to sort. just because the text contains numeric characters doesn't mean that the computer should figure out "oh, i bet he wants this to be sorted like numbers!" ... right ?

- Jeff

Jeff I understand that as I said, I did not get the datatype from the person who created the db, will have to ask him tomorrow.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources