need terminal digit sort 2

[srogers]

Does anyone have an idea on how to sort numbers in terminal digit sort?

That's where you sort a list of numbers by the last two digits and then the two before that and then the two before that as in the example below:

123498
123598
123698
123499
123599

There will be 6 or 7 digits in the numbers I'll need to sort.
I haven't tried anything yet because I'm not exactly sure where to start.

Thanks -

 

[nigelrivett]

order by right(convert(varchar(7),fld),2),
left(right(convert(varchar(7),fld),4),2),
left(right(convert(varchar(7),fld),6),2),
left(right(convert(varchar(7),fld),7),1)



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[vongrunt]

This should work too:

order by fld % 100, fld % 10000, fld % 1000000, fld % 100000000

 

[srogers]

Both of those worked.

Thank you so much -

 

[ESquared]

[tt]ORDER BY reverse(fld)[/tt]

 

[ESquared]

Oh... you might also be looking for

[tt]ORDER BY Reverse(Left(Convert(varchar(7),fld)+'0',7))[/tt]

Depending on how you want 6- or 7- character numbers to sort together.

 

[srogers]

Thanks for the contribution esquared - but I think that takes the number in reverse and sorts it in numerical order from there... ?

What I need to do is sort a number like this:

123600

by 00 and then by 36 and lastly by 12.

Thanks again -

 

[ESquared]

oh... I didn't pay attention, obviously!