ORDER BY, with a few exceptions!

[paparazzi]

I need a SQL SELECT statement that sorts by COUNTRY, with the following exceptions:

1) First, show all people from USA
2) Next, show all people from Canada
3) Then show people from remaining countries, sorted alphabetically by country name.

Output should resemble:

{TAB_PEOPLE}
NAME	COUNTRY
Bob	USA
Joan	USA
Brad	Canada
Krista	Canada
Mike	Afghanistan
Jill	Afghanistan
Don	Albania
Chloe	Albania
Vito	Bahamas
Steven	Bahamas
Vincent	China

I'm using Microsoft SQL Server 2005, and I don't have the ability to declare a stored procedures (simply don't have access). I can only read from the database, so it must be in the form of a single SELECT statement.

[SDas]

paparazzi, This gets you what you want:

WITH PEOPLE (NAME, COUNTRY)
  AS (SELECT 'VINCENT','CHINA' UNION ALL
      SELECT 'STEVE','BAHAMAS' UNION ALL
      SELECT 'VITO','BAHAMAS' UNION ALL
      SELECT 'CHLOE','ALBANIA' UNION ALL
      SELECT 'DON','ALBANIA' UNION ALL
      SELECT 'JILL','AFGHANISTAN' UNION ALL
      SELECT 'MIKE','AFGHANISTAN' UNION ALL
      SELECT 'KRISTA','CANADA' UNION ALL
      SELECT 'BRAD','CANADA' UNION ALL
      SELECT 'JOAN','USA' UNION ALL
      SELECT 'BOB','USA'
     )
SELECT NAME, COUNTRY
FROM (SELECT NAME
           , COUNTRY
           , CASE COUNTRY
               WHEN 'USA'    THEN 1
               WHEN 'CANADA' THEN 2
                             ELSE 3
             END AS SEQ_ORDER
     ) AS A
ORDER BY SEQ_ORDER
       , COUNTRY