Moving blank values to the end of sorted results

[foosh101]

In my search results, I give the user the ability to sort by a number of different fields. However, some of them don't always have values in them. When I do an 'ORDER BY Field', all records that do not have a value in them come up first. Is there any good way to make the blank values always show up at the end of the results, whether I sort descendingly or ascendingly. If the user wants to sort by that field, having several pages of records that have no value in that field are not really of any use to them. -Thanks

[John]

Not really (they will be at the end descending, as an empty string always compares less than any other string). If they are of no interest you could try removing them from the results altogether.

[foosh101]

yeah, thats what I figured. Unfortunately I can't remeove those results because while that field being blank would cause the need to have it show up at the end of the search results, it still need so show up in the results somewhere because it meets the users search criteria. I was just wondering if anyone else had a similar problem and/or texis had a neat way to solve for this issue.

[mark]

Maybe somewhat expensive, but for ascending sort you might sandr() the field changing empty to zzz or whatever it takes to be last. Then clear that special value before displaying it.

[foosh101]

Actually, that is a pretty good idea. I can do one better however, and do the replacement of the blank values before it even gets into texis so their is no need to do the sandr and reduce performance. Thanks for the help guys.

[mark]

If you set the value in the table the problem will be right back, but for descending sort instead of ascending.

[foosh101]

Yeah, I know. I realized the same thing about 5 minutes after I posted that. Thanks for the help.