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.