SQL Select Certain Value First

Question

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.

It is generally ordered on the primary key of your `Assets` table. Try setting the primary key(assuming it exists) of US & Canada to lowest possible values, that should work. — Sanjeevakumar Hiremath, Apr 18 '11 at 15:46
If it were me, I'd opt to do that in your ASP code. It's a presentation concern. If I were to tackle it server side, I'd put an ordinal column on the Assets table and "order by" on that value. — Pete M, Apr 18 '11 at 15:48
Even if it's possible with SQL I think you would win in performance by doing it in the asp code. — Niklas, Apr 18 '11 at 15:49
Primary keys are usually randomly selected and changing them for just one query is not a good idea. What if for a future Query only USA needs to be on top? See Martin's response below. That's a more appropraiate way to solve this problem. — Rajesh Chamarthi, Apr 18 '11 at 15:50

score 23 · Accepted Answer · answered Apr 18 '11 at 15:47

23

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

answered Apr 18 '11 at 15:47

Martin Smith

438,706
87
741
845

Great idea! I usually add a column to the output result set, and order on it, but putting the ordering value in the ORDER BY clause is a great way to do it. – François Beausoleil Apr 18 '11 at 15:50
@Francois- That approach could use an index better but it looks as though this is possibly some type of EAV thing where the `AssetValue` column contains a mix of different things anyway (not just countries). – Martin Smith Apr 18 '11 at 15:55
2

Solution is good in short term, however, it's a hard coded solution. By the looks of it, Assets is a generic table. What if 'US' is changed to 'United States'? And what if OP has the need to do this kind of ordering in other asset types? If he needs that flexibility, he would need to go for a more generic solution. – Adriano Carneiro Apr 18 '11 at 15:55
I agree with @Adrian. It is a great work around, but I wouldn't do this as a long term solution. I also wouldn't count on the remaining AssetValues coming back in a particular order in the long term. – Pete M Apr 18 '11 at 15:58
@Adrian - True - Only they can determine if they need that flexibility for other `AssetType`s or whether it would be redundant for those. @PeteM - Not sure what your last sentence means? I'm not counting on anything. `AssetValue` is the 2nd `ORDER BY` column. – Martin Smith Apr 18 '11 at 15:59
I have posted a generic solution, please take a look. – Adriano Carneiro Apr 18 '11 at 16:00
2

In the Short term this should work for me, but as soon as I can I will add the new Column to determine order preference. That was my goal in the first place, But I cant edit the DB as of yet. So thanks everyone. – atrljoe Apr 18 '11 at 16:11
The `ELSE` part wasn't necessary in my case. – felipe.zkn Jul 22 '16 at 13:31

Adriano Carneiro · Answer 2 · 2011-04-18T15:58:18.883

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.

score 1 · Answer 3 · answered Apr 18 '11 at 15:47

1

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.

answered Apr 18 '11 at 15:47

BugFinder

17,474
4
36
51

SQL Select Certain Value First

3 Answers3