how to order string logically

Q:

I have the following case :

set of letters (grades) A,A+,A-,B,B+,B- stored as strings in the database i wanna to order these grades logically from the small one to the big one ,, but this not what happen in real.. because these are strings the order is:

A,A+,A- i wanna

ASC

A-,A,A+

DESC

A+,A,A-

i bind those grades in dro开发者_JAVA百科p down list and i wanna these grades with this logical order in it..

is there any idea how to do something like this..

---

Keep a separate table which lists the grades and what order they should go in:

Grade | SortOrder
B-    | 20
B     | 30
B+    | 40
A-    | 50
A     | 60
A+    | 70

Then your Select query can do an INNER JOIN to this table and ORDER BY SortOrder.

---

Convert the grades to a numerical representation and then sort on that.

---

Very crude implementation:

public double getScore(String grade)
{
    grade = grade.ToUpper();
    if(grade.Length > 2 || grade.Length <= 0)
    {
        throw new ArgumentException();
    }
    var baseGrade = (double)grade[0];
    if(baseGrade < 65 || baseGrade > 90)
        throw new ArgumentException();

    if(grade.Length == 2)
    {
        var gradeShift = grade[1];
        switch(gradeShift)
        {
            case '+':
                baseGrade -= 0.3;
                break;
            case '-':
                baseGrade += 0.3;
                break;
            default:
                throw new ArgumentException();
        }
    }

    return baseGrade * -1 + 90.5;
}

---

Use numbers representing grades

Or overload comparison operators

---

Here is a SQL select statement for a table (Table1) containing a field Grade.

select Grade
from Table1
order by
    case Grade
        when 'A+' then 5
        when 'A'  then 4
        when 'A-' then 3
        when 'B+' then 2
        when 'B'  then 1
        when 'B-' then 0
        else -1     
    end asc

---

Here's a working solution:

WITH ExamResults AS
(
    SELECT 1 ResultId, 'Joe Blow' Student, 'A-' Grade
    UNION ALL SELECT 2, 'Leroy Jones', 'B+'
    UNION ALL SELECT 3, 'Paul Smith', 'B-'
    UNION ALL SELECT 4, 'Helen Potter', 'B'
),
Suffix AS
(
    SELECT '+' Suffix, 1 [Rank]
    UNION ALL SELECT '', 2
    UNION ALL SELECT '-', 3
),
Grade AS
(
    SELECT DISTINCT Grade, LEFT(Grade, 1) Letter, SUBSTRING(Grade, 2, 1) Suffix
    FROM ExamResults
)
SELECT ExamResults.*,
    Grade.Letter,
    Grade.Suffix,
    Grade.Letter + CAST(Suffix.[Rank] AS char(1)) GradeOrder
FROM ExamResults
    JOIN Grade ON Grade.Grade = ExamResults.Grade
    JOIN Suffix ON Suffix.Suffix = Grade.Suffix
ORDER BY
    Grade.Letter,
    Suffix.[Rank]

Output:

ResultId    Student      Grade Letter Suffix GradeOrder
----------- ------------ ----- ------ ------ ----------
1           Joe Blow     A-    A      -      A3
2           Leroy Jones  B+    B      +      B1
4           Helen Potter B     B             B2
3           Paul Smith   B-    B      -      B3

---

You could make use of enumerators for that matter, so the you could sort easier.