Interesting SQL collation problem

Question

The crunch time, the deadline for my last contract expires in two days, and almost everything is completed and working fine (knock on wood), except for one problem.

In one of my stored procedures, I need to return a result set as follows.

group_id      name
--------      --------
A101          Craig
A102          Craig
Z101          Craig
Z102          Craig
A101          Jim
A102          Jim
Z101          Jim
Z102          Jim
B101          Andy
B102          Andy
Z101          Andy
Z102          Andy

Names must be sorted by the first character of the group identifier, and also contain entries Z101 / Z102. Matching strictly by group id, I get a result set as follows:

group_id      name
--------      --------
A101          Craig
A102          Craig
A101          Jim
A102          Jim
B101          Andy
B102          Andy
Z101          Andy
Z102          Andy
Z101          Craig
Z102          Craig
Z101          Jim
Z102          Jim

I really can’t come up with a solution that doesn’t assume that I am making the cursor and bloating the stored procedure more than it already is. I am sure that a great mind there has an elegant solution, and I really want to see what the community has to offer.

Thanks for the ton in advance.

: :) , , .

. , -, , , , , .

, , /, . Z "" .

, . , , . .

, , . .

, :

    ORDER BY    candidate.party,
            candidate.ballot_name,  
CASE WHEN       candidate.district_type = 'MAG' THEN LEFT(votecount.precinct_id, 1) END,
        candidate.last_name,
        candidate.first_name,
        precinct.name

2: , (1:43 A.M.) -

:

    IF          candidate.district_type = 'MAG'
BEGIN
    (
        SELECT candidate.id AS candidate_id, candidate.last_name, LEFT(votecount.precinct_id, 1) AS district, votecount.precinct_id
        FROM candidate
        INNER JOIN votecount
        ON votecount.candidate_id = candidate.id
        GROUP BY name
    ) mag_order
    INNER JOIN      mag_order
    ON              mag_order.candidate_id = candidate.id
END

mag_order.district, .precinct_id, .last_name.

- SQL (SELECT) mag_order. - - ? . , .

Answer 1

SELECT g1.group_id, g1.name
FROM 
    groups g1
        INNER JOIN
    (
        SELECT  MIN(group_id), name
        FROM groups
        GROUP BY name
    ) g2 on g1.name = g2.name
ORDER BY g2.group_id, g1.name, g1.group_id

Answer 2

ORDER BY DESC, SUBSTR (group_id, 1), group_id

Answer 3

SELECT groupId, name
FROM table
ORDER BY getFirstGroupId(name), name, groupId

getFirstGroupId() Id

SELECT MIN(groupId)
FROM groupTable
WHERE name = @name