| Author |
Topic |
|
pen
Starting Member
4 Posts |
 Posted - 2009-04-01 : 04:48:50
|
| Hi guys!I have this question:i want to sort a table by a field that could contain both numerical or string data. in case it's numerical i found out i can use ORDER BY val (field) but if it's a string it gives an error of course. i can't leave it as ORDER BY field either cause then it sorts numbers like: 1,11,12,2,25 etcis there any way i can sort the data whether they are strings or numeric? i was thinking i could add a 0 in front of the numeric ones but i don't know how to do that in sql. I have an MS Access database and the query is like:SQL="SELECT TOP 150 * FROM datakod1 where eidos='"&eidos&"' AND aa like '"&aa&"%' AND arthro Like '"&arthro&"%' ORDER BY aa, arthro,paragr "the field "arthro" is the one containing both numeric and string types.does anyone know how to do it?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-01 : 05:00:17
|
ORDER BY LEN(Field), Field
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
pen
Starting Member
4 Posts |
Posted - 2009-04-01 : 05:14:00
|
| sorry, this doesn't work, it only sorts by length |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-01 : 05:30:27
|
No. It sorts primarily by length and then by content.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
pen
Starting Member
4 Posts |
Posted - 2009-04-01 : 05:41:01
|
| it doesn't work the way i want though.. value could be 1,2,3 but it could also be 18-20. in this case value 63 will go before 18-20 because of the length.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-01 : 06:22:51
|
quote:
Originally posted by pen
it doesn't work the way i want though.. value could be 1,2,3 but it could also be 18-20. in this case value 63 will go before 18-20 because of the length..
Post some sample data with all possible casesMadhivananFailing to plan is Planning to fail |
|
|
|
pen
Starting Member
4 Posts |
Posted - 2009-04-01 : 06:55:18
|
| there are numbers like 1,2,5,45, 89, but also like 32-36, 15-19and there's also other data like ?/2148 or 10/1563 but it doesn't necessarily mean that all those would be displayed together, it depends on what kind of filter is used too. it's a bit complicated..this is why i think it would only work right if i somehow put 0 in front of numbers 1-9 but it shouldn't be displayed like that |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-01 : 06:58:55
|
| put them in a table format, the way you have it in your table. Post another table that displays the expected output. |
|
|
|
wakkowarner
Starting Member
4 Posts |
Posted - 2009-04-01 : 11:55:20
|
| Technically, you aren't sorting something that contains either numerical or string data, if it is text, then it is string data. Sorting by that returns the results you got, and it makes sense for strings. If your data really is numerical, then you need to convert. If your field is string because you have to store things like hyphens, but you still would like to see a "numerical" ordering, then you must determine what in the string determines the numerical order. Is a number ALWAYS present? The easiest way for us to get a picture of this is to do what sakets asked, give us some sample data covering all the different scenarios, and what you would like as output.So, given your sample data (I'm randomizing it):?/2148, 5, 45, 89, 32-36, 15-19, 2, 10/1563, 1How do you want it ordered? Do you want all numbers to go first, followed by the strings? Do you want to extract the numerical data and order by that? Like this:1, 2, 5, 10/1563 (sorting by the '10'), 15-19 (sorting by '15'), 32-36 (sorting by '32'), 45, 89, ?/2148 (sorting by '2148') |
|
|
|
dsindo
Starting Member
45 Posts |
Posted - 2009-04-20 : 18:00:15
|
| ORDER BY cast(field as binary) |
|
|
|
dsindo
Starting Member
45 Posts |
Posted - 2009-04-20 : 18:03:44
|
| OR ORDER BY case when isnumeric(field)=1 then '0' + cast(field as varchar(100)) else cast(field as varchar(100)) end |
|
|
|
|
sorting numeric and string data