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
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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