Crystal Reports 2008 and Oracle 11g - Order by Suffix, D before A

[hmax]

I capture all results for a site like, for example:

ABC000012D
ABC000012A5
ABC000012A6
ABC000012A7

Any others I do not capture at this time.
ABC can be any length, greater or smaller.
000012 can be any length, greater or smaller.

I must order by

D
A5
A6
A7

if all of these exist, because A5, A6, A7 are sub-sites; however the relationship do not exist as one would expect. D may exist alone. D may only have A5 in addition.

The IDs relating to these data are all the same for the site, they are not unique.

Therefore, I need to build the logic to order in this way. When I group on the fields, "ABC00012%", it defaults, of course, to As before D. I have worked with right({ABC00012},1) and right({ABC00012},2), but am not quite there.

Thank you for your suggestions.

 

[IanWaterman]

Use a formula like

If isnumeric(right(yourfield, 1)) = true then right(yourfield, 2) else right(yourfield, 1)

Sort or group on this formula descending

Ian

 

[lbass]

If right(code)="D" then 1 else 2

Sort by this formula ascending and then add a sort by code ascending.

-LB

 

[hmax]

Thanks, IanWaterman and lbass!

-hmax