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
238 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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

22 Experts available now in Live!

Get 1:1 Help Now