Stump the Panel: SharePoint Q&A

I'm tracking projects (List of Projects) with multiple task items (List of Project Ref # and a dozen task items per). Each of the dozen tasks per project has it's own separate status field.

The status possibilities are:
Critical [Worst]
Advisory [Bad]
Verified [Good]
Created [Best]

I need to present the single record per Project Ref # with the worst status and/or earliest date.
My plan was to sort on "Status" from Worst to Bad, then display the distinct row based on Ref # (should be first row after the sort above).

I have attempted several different ideas to force the sort order of "Status" using the "xsl:sort" function, but none are working.

Obviously, there is no 'natural' sort order. Values not alphabetical and have no numerical sequence.
When I sort the rows based on this field, I can only get it to display as:
Advisory
Created
Critical
Verified

Can someone help me find the best way in a DVWP using XSL and/or a complex Xpath sorting expression to sort this way and then present the first record of each set of repeating "ref #"?

The easy answer would be to change the choice values to
1-Critical
2-Advisory
3-Verified
4-Created
and then use ascending sorting on that column.

When there isn't a natural sort order, you have to create one somehow.

I appreciate the input - and was afraid that might be the case. My users are not very fond of the idea. What about creating a list starting with the worst to best, and then filtering based on the order of the ID's in that list? Do you think that would work?

Another alternative would be to use a workflow to update a hidden sort column on creation. Based on the choice, you set the field to 1, 2, 3, or 4 and can sort and filter on that field.

You could create a calculated column that represents your sort order. That would have less moving parts and a little more flexible if you want to add more logic to your sorting later on down the road.

That works too, or could be done via a jQuery PreSaveAction on creation, lots of options.