ORDER BY, with a few exceptions!

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:

Code:

---

{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.

paparazzi, This gets you what you want:

Code:

---

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

---