SQL Select Certain Value First

SELECT     AssetValue
FROM         Assets
WHERE     (AssetType = 'Country')

Very simple Select Statement, but it outputs

Afghanistan
Albania
Algeria
....

How do I make United States and Canada appear on the top of this? Is this 开发者_运维百科possible with SQL or do I need to do something in my ASP code? My PK is a INT Identity.

SELECT     AssetValue
FROM         Assets
WHERE     (AssetType = 'Country')
ORDER BY CASE AssetValue 
          WHEN 'US' THEN 1 
          WHEN 'Canada' THEN 2 ELSE 3 END, AssetValue 

If you do not wish to hard code, the most generic solution is having an extra field to indicate the preferential items. You would call it OrderPreference. The higher OrderPreference, the first the item would appear. Every item else would have OrderPreference equals ZERO. The query:

SELECT     AssetValue
FROM         Assets
WHERE     (AssetType = 'Country')
ORDER BY OrderPreference desc, AssetValue

By the looks of it, Assets is a table you use for other things (not only countries), so you could use this approach to solve the same problem for others asset types, if they come up.

Give them a a group ID, so group 1 is US, UK etc, group2 is the rest of the world, then you can do "priority" countries.