Home > Archive > PHP SQL > January 2005 > Help needed for SQL sort routine
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help needed for SQL sort routine
|
|
|
| I am running a database query and need to be able to sort the results by a
particular field that has the following values:
1a, 1b, 2a, 2b, 3a, 3b, 4a, 4b, 5a, 5b, 6a, 6b, 7a, 7b, 8a, 8b, 9a, 9b, 10a,
10b, 11a, 11b and so forth.
An ordinary ascending or descending sort is no use as it will place 10a, 11b
etc before 2a for example. All I can think of is to get the result into an
array unsorted and then do a customised sort. Is there any way I can use a
customised sort routine during the actual SQL query though?
Any help greatly appreciated.
| |
|
| On Wed, 19 Jan 2005 00:39:28 GMT, Alex <alex@home.com> wrote:
>I am running a database query and need to be able to sort the results by a
>particular field that has the following values:
>
>1a, 1b, 2a, 2b, 3a, 3b, 4a, 4b, 5a, 5b, 6a, 6b, 7a, 7b, 8a, 8b, 9a, 9b, 10a,
>10b, 11a, 11b and so forth.
>
>An ordinary ascending or descending sort is no use as it will place 10a, 11b
>etc before 2a for example. All I can think of is to get the result into an
>array unsorted and then do a customised sort. Is there any way I can use a
>customised sort routine during the actual SQL query though?
>
>Any help greatly appreciated.
Please ignore my original posting as now I've learned that I just need
to add a '+0' to the search term to do the sort correctly.
| |
| Bob Stearns 2005-01-20, 3:57 am |
| Alex wrote:
> On Wed, 19 Jan 2005 00:39:28 GMT, Alex <alex@home.com> wrote:
>
>
>
>
>
> Please ignore my original posting as now I've learned that I just need
> to add a '+0' to the search term to do the sort correctly.
>
>
If that works it is only by accident. It is (depending on your SQL)
doing the best job it can of converting the column's contents to a
number, by dropping the letter suffix, then sorting on that.
Correct would be some variation of
order by cast(substr(f,1,length(f)-1) as integer), substr(f,length(f),1)
where f is the field to sort on. Since this is untested code, there are
probably syntax errors etc.
|
|
|
|
|