Order By Help - need to order a different way?

[LCD]

here is a example of my problem.

Select ID, FileName From Table
Order By FileName

Returns

ID | FileName

 1 | A0-01-Layout1 (2002.10.30.1.21.33.235).TIF
 2 | A1-03-Layout1 (2002.10.30.1.21.38.131).TIF
 3 | A1-2-Layout1 (2002.10.30.1.21.34.197).TIF
 7 | A3-1-Layout1 (2002.10.30.1.21.42.127).TIF
 4 | C1 (2002.12.19.2.9.36.201).TIF
 8 | C11 (2002.12.19.2.9.42.89).TIF
 6 | C12 (2002.12.19.2.9.42.130).TIF
 9 | C4 (2002.12.19.2.9.38.144).TIF
 5 | C9 (2002.12.19.2.9.40.137).TIF

And here are my desired results

ID | FileName

 1 | A0-01-Layout1 (2002.10.30.1.21.33.235).TIF
 3 | A1-2-Layout1 (2002.10.30.1.21.34.197).TIF
 2 | A1-03-Layout1 (2002.10.30.1.21.38.131).TIF
 7 | A3-1-Layout1 (2002.10.30.1.21.42.127).TIF
 4 | C1 (2002.12.19.2.9.36.201).TIF
 9 | C4 (2002.12.19.2.9.38.144).TIF
 5 | C9 (2002.12.19.2.9.40.137).TIF
 8 | C11 (2002.12.19.2.9.42.89).TIF
 6 | C12 (2002.12.19.2.9.42.130).TIF

As you can see, A1-2 and A1-03 switched and the FileNames starting with 'C' are in order.
Can anyone suggest a way to get these results or is it just impossible?

Thank You
Andrew

 

[r937]

it is not impossible, but it is tedious beyond belief

for example, to get the Cs in order, use CHARINDEX to find the first blank, and let's call this number B

then use SUBSTRING to pull out the string starting at position 2 and going for a length of B-2, let's call this string X

then CAST X as SMALLINT

now nest those function calls so that they are a single expression, and put that in the ORDER BY

but wait, there's more!!

to do the As, you have to use a different expression

here, you have to CHARINDEX the second dash, and SUBSTRING from the first

so when you get that expression worked out, combine it with the first one in a CASE expression

but how will you determine which it is, A or C? probably by testing to see whether the 2nd dash exists, because if you test to see whether the blank exists, you will find one in the As

oh, and perhaps you'd better use the ISNUMERIC function in there as well, just in case you run into a file name that is not like your As and Cs, with neither a blank nor a dash...

is any of this making sense?

still wanna do it?




rudy

http://r937.com/

SQL Consulting