Order By Statement

I have the following SQL statement

 CURSOR player_cursor IS
    select *
    from customer_data.cd_soccer_players p
    where p.team_id = tID
    and p.league_id = p_leagueID
    and p.status = 'Y'; 

Code (markup):

I would like to order the following statement by a column called player_position. Player position has four different options: goalie, forward, center, defense. I would like to have the player be ordered in the following order:
1. Forward
2. Defense
3. Goalie
4. Center

Any help would be greatly appreciated

 

Well, there are a couple of solutions. I will give you a couple of options and let you choose or at least get you thinking about other options. I have used a sortorder column populated with the desired sort order of course. I have used this many times for custom sort orders (i.e. letter content, look up values, some crazy business requirements, etc). where an ALPHA sort will not work.

Crude example:

Position
PositionRecID
PositionName
SortOrder

Position
1,"Center",4
2,"Defense",2
3,"Forward",1
4,"Goalie",3

I would join with dbo.Position ORDER BY Position.SortOrder. This is assuming that the positions are normalized and not in the Player table. If the position name text is part of the player table, then we need another approach. Let me know, before I continue on assumptions.

 

Try the following query :

CURSOR player_cursor IS
select *
from customer_data.cd_soccer_players p
where p.team_id = tID
and p.league_id = p_leagueID
and p.status = 'Y'
order by
(case p.player_position
when 'forward' then 1
when 'defense' then 2
when 'goalie' then 3
when 'center' then 4
else 5
end);

cheers,

Royal