Search code examples
ms-accesssql-order-byunion

MS Access -- Using Order By in a Union Query

I have a need for a query that will select the CountryName from a table named Country such that the "United States" always comes out on top, and all the remaining countries follow in Alphabetical order. I used the following code, the intent of which is to select only the United States, and then use the Union to add all other countries after the United States in alphabetical order. It seemed to work, but I then noticed that when a few additional countries were added to the table, the United States was on top as desired, but the added countries were just appended to the query results (that is, not in alphabetical order as desired). Any thoughts? I'm not sure if what I want is even possible in Access. "I'm using Access version with Office 360.)

SELECT * from 
( 
SELECT Country.CountryDesc FROM Country where (((Country.CountryDesc)="united states"))
) dummy_alias1

UNION ALL SELECT * from
(
SELECT Country.CountryDesc  FROM Country WHERE (((Country.CountryDesc)<>"united states")) 
ORDER BY Country.CountryDesc
) dummy_alias2;
Solution

  • As you noticed, UNION ignores ORDER BY of subqueries. However, don't need a UNION query. Calculate a value to base sorting on:

    SELECT Country.* 
FROM Country 
ORDER BY IIf(CountryDesc = "United States", 1, 2), CountryDesc;