Interesting SQL Sorting Issue

Posted by rofly on Stack Overflow See other posts from Stack Overflow or by rofly
Published on 2010-05-06T04:31:40Z Indexed on 2010/05/06 4:38 UTC
Read the original article Hit count: 641

Filed under:
|
|

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:

A101 | Craig
A102 | Craig
A101 | Jim
A102 | Jim
B101 | Andy
B102 | Andy
Z101 | Andy
Z102 | Andy
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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about stored-procedures