Changing Ordering

Not quite sure how to word this.
Is there any way to change the ordering of SQL Server
I need it to order
ABC2 then ABC12 then ABC110 (this is by entire string, rather than
individual characters)
currently it orders it ABC110, ABC12, ABC2
is there anyway we can change this?
On Thu, 2 Feb 2006 02:11:27 -0800, Sian wrote:

>Not quite sure how to word this.
>Is there any way to change the ordering of SQL Server
>I need it to order
>ABC2 then ABC12 then ABC110 (this is by entire string, rather than
>individual characters)
>currently it orders it ABC110, ABC12, ABC2
>is there anyway we can change this?
Hi Sian,
ORDER BY ColumnName DESC
?
Hugo Kornelis, SQL Server MVP
|||Sorry I don't think I explained my problem properly. I can't just use desc
as this isn't what I need.
For example, I have the following parts on the shelf, in this (logical) order:
Table 1
FDB2
FDB12
FDB125
FDB1000
FDB2100
However, if I run a report that includes these part numbers on T21, the
report sorts them as follows:
Table 2
FDB1000
FDB12
FDB125
FDB2
FDB2100
I can see the sorting logic here (ie part no. is not an integer and
therefore it is read and sorted as text - from left to right) but I’m afraid
this sort order (Table 2) is no good.
We need a system option to manage/redefine sort orders for alpha-numeric
part numbers on all reports that contain part number lists.
|||"Sian" <Sian@.discussions.microsoft.com> wrote in message
news:D7CC01E7-6CD8-4DEC-B621-4F2889B1BCFA@.microsoft.com...
> Not quite sure how to word this.
> Is there any way to change the ordering of SQL Server
> I need it to order
> ABC2 then ABC12 then ABC110 (this is by entire string, rather than
> individual characters)
> currently it orders it ABC110, ABC12, ABC2
> is there anyway we can change this?
If the structure is consistant: 3 caracters followed by n figures I'd sort
on the value of the string minus the first 3 positions. Something like:
Order on VAL(MID([Myfield];4))
Or use another trick to get rid of all but figures '0123456789'
Function NumOnly(strSource) As String
' Get rid of all but figures
' on the fly adapted from other routine and untested
Dim strOK As String
Dim strPart As String * 1
Dim intLus As Integer
Dim intPlaats As Integer
NumOnly = ""
strOK = "0123456789"
While Len(strSource) > 0
strPart = Left(strSource, 1)
strSource = Mid(strSource, 2)
intPlaats = InStr(1, strOK, strPart)
If intPlaats > 0 Then
NumOnly = NumOnly + strPart
End If
Wend
End Function
and go with Order on VAL(NumOnly([MyField])
|||On Mon, 6 Feb 2006 02:11:30 -0800, Sian wrote:

>Sorry I don't think I explained my problem properly. I can't just use desc
>as this isn't what I need.
>For example, I have the following parts on the shelf, in this (logical) order:
>Table 1
> FDB2
> FDB12
> FDB125
> FDB1000
> FDB2100
>However, if I run a report that includes these part numbers on T21, the
>report sorts them as follows:
>Table 2
> FDB1000
> FDB12
> FDB125
> FDB2
> FDB2100
>I can see the sorting logic here (ie part no. is not an integer and
>therefore it is read and sorted as text - from left to right) but Im afraid
>this sort order (Table 2) is no good.
>We need a system option to manage/redefine sort orders for alpha-numeric
>part numbers on all reports that contain part number lists.
>
Hi Sian,
I was afraid of this...
The problem is caused by a flawed design. Apparently, these part numbers
are not a single code, but a concatenation of two attributes. And at
least one of those attributes has more meaning than just identifying a
part.
Since "FDB" and "1000" are two distinct attributes, they should have
been stored in two distinct columns. You can always concatenate them in
the front-end, in a view, or -if you really must- in a computed column
in the base table.
Until you get your design fixed, you can try using this kludge as a
temporary workaround:
ORDER BY LEFT(BadColumn, 3),
CAST(SUBSTRING(BadColumn, 4, LEN(BadColumn) - 3) AS integer)
However, this *will* break if you have any data in the table where the
part number doesn't follow the same layout. And it will perform very
bad, because the optimizer can't leverage an index on the BadColumn to
facilitate the sorting operation.
Hugo Kornelis, SQL Server MVP