Solved

Is their a way to have a Select statement order by fields but choose which fields are on top?

Posted on 2008-06-12
6
249 Views
Last Modified: 2010-03-19
I want to have a Select statement Order by a region ID but have region id = 'HTX' show up above the other region IDs, is this possible in a select statement?  

Example

Select * from Region Order By regionID 'HTX' first

Results:
HTX
HTX
HTX
OH
OH
PA
YOH
YOH
0
Comment
Question by:higginsonline
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21771271
something like this should take care of what you need....

Select * from Region Order By case when regionID 'HTX' then 'AAA' ELSE Region END ASC
0
 

Author Comment

by:higginsonline
ID: 21771384
SELECT     *
FROM        Region
ORDER BY case when Region 'PA' then 'AAA' ELSE Region END ASC

I tried what you suggested with the above and I am getting Incorrect syntax near 'PA'
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21771426
missed a = sign....my bad.


SELECT     *
FROM        Region
ORDER BY case when Region = 'PA' then 'AAA' ELSE Region END ASC
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:higginsonline
ID: 21771489
Is there a special feature for blank fields (Not Null but Blank = '')  Because it does not seem to be working the way I want it to, it is first bringing me all the RegionID = '' Then all the ones I specified in the clause
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21771514
try this:


SELECT     *
FROM        Region
ORDER BY case when Region = 'PA' then 'AAA' when LEN(LTRIM(RTRIM(ISNULL(region,'')))) = 0 THEN 'ZZZ' ELSE Region END ASC
0
 
LVL 2

Expert Comment

by:vasureddym
ID: 21771545
check this code.. not a straight solution but will give what you need
create table #Region (regionID varchar(10))
 

insert into #Region values('AH')

insert into #Region values('AA')

insert into #Region values('OH')

insert into #Region values('PA')

insert into #Region values('YOH')

insert into #Region values('HTX')

insert into #Region values('OH')

insert into #Region values('HTX')

insert into #Region values('YOH')

insert into #Region values('HTX')
 

Select * from #Region 

Order By case 

when regionID 'HTX' then 'AAA' ELSE Region END ASC
 

Select * from #Region 

Order By regionID
 

Select * from #Region 

where regionID = 'HTX'

UNION ALL

select * from 

	(Select top 100 percent * from #Region 

	where regionID <> 'HTX'

	Order By regionID

	)a

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now