|
| |
|
|
pd_it_guy
Posts: 215
Joined: 3/4/2008
Status: offline
 |
SQL sort troubles - 8/31/2009 11:40:21
I have a small database to keep track of portable radios within an ASP web. One of the attributes or each record is the radio number which was originally entered as a number. The data element is r_id and they go from 1 to about 200. I want the display to be in order of the radio number so I added the following to my SQL:
...ORDER BY r_id"
What I get is something like 1,11,111,2,21,221,3 and so forth. It either thinks r_id is text, or is ignoring everything but the first character. Can I do something for the SQL sort to handle the variable "r-id" as a the number it really is?
Thank you and all ideas appreciated.
|
|
|
|
ou812
Posts: 1705
Joined: 1/5/2002
From: San Diego
Status: offline
|
RE: SQL sort troubles - 8/31/2009 12:32:05
Not sure what DB you're using, but if it is access you should be able to do this:
ORDER BY cint(r_id)
_____________________________
-brian
Black Holes suck.
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
pd_it_guy
Posts: 215
Joined: 3/4/2008
Status: offline
|
RE: SQL sort troubles - 8/31/2009 12:42:39
Sorry, it's Access.
I did some digging and came to find out that Access changed the data type to text, so the sort order from what it had to work with was actually working right. Let me try CINT and report back.
| |
|
|
|
pd_it_guy
Posts: 215
Joined: 3/4/2008
Status: offline
|
RE: SQL sort troubles - 8/31/2009 12:49:07
CINT(r_id) errored out I am guessing because there were some text entries (other than numbers ending up as text) that simply could not be converted.
Is it possible to test for and skip over the CINT conversion if the value cannot be converted. That way, the numbers sort properly and the text ends up at the very end.
The radios are either all numbers, or have some alpha characters in them.
| |
|
|
|
ou812
Posts: 1705
Joined: 1/5/2002
From: San Diego
Status: offline
|
RE: SQL sort troubles - 8/31/2009 16:05:27
I'm sure there are many ways to go about this, but the first that comes to mind would be to try and separate the data (I'm not thinking of anything off hand to allow you to sort and skip over record while doing it). Maybe make another column for the alpha data. So the r_id column would have only ints and, say r2_id, would have the alpha with r_id=999 or something. Then you can sort on the r_id. Not sure if that makes sense, but it is just a thought. And again, there are probably other ways to do this, and probably better too!
_____________________________
-brian
Black Holes suck.
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
pd_it_guy
Posts: 215
Joined: 3/4/2008
Status: offline
|
RE: SQL sort troubles - 8/31/2009 19:16:40
Thanks- you've given me some great ideas.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 