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.

Answers


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.


Need Your Help

How to compare 2 dataTables

c# .net datatable

I have 2 datatables and I just want to know if they are the same or not. By "the same", I mean do they have exactly the same number of rows with exactly the same data in each column, or not. I'd l...

get the last element in doctrine2/symfony2

symfony doctrine-orm

i have relation ManyToOne between two entity Collaborateur and Conge.