?
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
Medium Priority
?
284 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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