Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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

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
higginsonline
Asked:
higginsonline
  • 3
  • 2
1 Solution
 
chapmandewCommented:
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
 
higginsonlineAuthor Commented:
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
 
chapmandewCommented:
missed a = sign....my bad.


SELECT     *
FROM        Region
ORDER BY case when Region = 'PA' then 'AAA' ELSE Region END ASC
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
higginsonlineAuthor Commented:
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
 
chapmandewCommented:
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
 
vasureddymCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now