SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
-
Dec 14, 2009, 09:47 #1
ORDER BY problems depending upon type
I want my query to ORDER BY number, but I have a problem...
I have the following numbers...
1
4
22
28T
I my number column is of type int, I lose the "T" in 28T.
If I make it of type CHAR, it orders it as follows:
1, 22, 28T, 4
4 should be before 22. Can anyone help with this? One way I can get it to work is to keep it as a CHAR and enter 1 and 4 as 01 and 04, but i don't want to have to do that.
Anyone?Restaurant Takeout & Delivery Menus : Over 4500 Menus Online
Athletic Recruiting & Community for High School Athletes!
-
Dec 14, 2009, 09:59 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:ORDER BY CAST(colname AS UNSIGNED)
-
Dec 14, 2009, 10:01 #3
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
If you want to treat it like a numeric, then the field should be numeric. That means whatever situation makes the T in the field should be removed. If you've got multiple sub-groups under one number, make it an additional field, then sort it by both of them.
In other words, change your structure to be like this:
OrderByField int,
OrderBySubField char
Then your order by portion of the SQL statement would be ORDER BY OrderByField, OrderBySubFieldDave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Dec 14, 2009, 10:02 #4
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Dec 14, 2009, 10:09 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 14, 2009, 10:14 #6
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Good thing I'm not really curious then, cause unsigned doesn't work in SQL Server 2005.
Msg 243, Level 16, State 1, Line 1
Type UNSIGNED is not a defined system type.Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Dec 14, 2009, 10:26 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
oh, now i'm embarrassed, because CAST AS UNSIGNED is mysql syntax
the original poster did not indicate which database system this is, so you shouldn't assume sql server either
-
Dec 14, 2009, 10:40 #8
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Dec 17, 2009, 11:40 #9
I'm using mysql
Restaurant Takeout & Delivery Menus : Over 4500 Menus Online
Athletic Recruiting & Community for High School Athletes!
-
Dec 17, 2009, 12:38 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 19, 2009, 18:48 #11
r937,
Thank you, that worked perfectly. It properly orders a VARCHAR column that has numbers and letters in it!Restaurant Takeout & Delivery Menus : Over 4500 Menus Online
Athletic Recruiting & Community for High School Athletes!
Bookmarks