Interesting SQL Sorting Issue

Question

It's crunch time, deadline for my most recent contract is coming in two days and almost everything is complete and working fine (knock on wood) except for one issue.

In one of my stored procedures, I'm needing 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

The names need to be sorted by the first character of the group id and also include the Z101/Z102 entries. By sorting strictly by the 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 think of a solution that doesn't involve me making a cursor and bloating the stored procedure up more than it already is. I'm sure a great mind out there has an elegant solution and I'm eager to see what the community can come up with.

Thanks a ton in advance.

Edit: Let me expand :) I'm sorry, it's late and I'm coffee addled.

The above result set is a special case for a special type of data entry. Being transparent, we're making an election based website and these are going to be candidates sorted by office, name, and then district.

Most offices have multiple districts in them except for district positions like magistrate/coroner, which will have only one. The Z comes in as the "district" for absentee machine and absentee paper votes.

The non-magistrate positions can be sorted by name first, as they are all grouped together. However, the existing system lists all magistrates in a huge clump of information, when they should be sorted by individual districts. This is where the issue lies.

To protect my pride, I want to add that I had no control over the normalization of the database. It was given to me by the client.

Here's the order clause of my stored procedure, if it helps:

    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

Edit 2: Here's where I currently stand (1:43 A.M.) -

I'm using a suggestion below to create a conditional inner join as follows:

    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

and then I'll sort it by mag_order.district, candidate.precinct_id, candidate.last_name.

For some reason I'm getting a SQL error when aliasing the ( SELECT ) as mag_order. Anyone see anything wrong with the code? I can't for the life of me. Sorry this is a bit tangential.

Answer 1

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

Answer 2

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 3

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

Then your getFirstGroupId() function would return the first groupId for that name

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