ORDER BY with one entry out of order

Question

ORDER BY with one entry out of order

Noc234234

Old Hand

Points: 335
More actions
September 28, 2011 at 12:54 pm

#258023

I have a field I need to order alphabetically, but I want one of the entries to be at the top
no matter what, the field name is USERS and the entries are like this:
Programmers, Admins, All, Developers, Webmasters
I want the sorting to be:
All
Admins
Developers
Programmer
Webmasters
Order by ASC would work except that Admins will be above All, I want All at the top, and the
rest alphabetical. ('All' is actually a value in the field, just like the others).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is there another field in the database (such as PK) that would help in sorting this data in the desired way?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

You just need to get a little tricky in your sort.

Something like this should work.

create table #Users

(

val varchar(20)

)

insert #Users

(val)

values

('All'),

('Admins'),

('Developers'),

('Programmer'),

('Webmasters')

select * from #Users order by case val when 'All' then '_' else val end

That should work as long as you don't have a value in your table that would sort alphabetically earlier than '_'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 3

select *

from dbo.MyTable

order by case Users when 'ALL' then 1 else 2 end, Users ;

That avoids even things like entries that begin with low-ASCII-value punctuation coming before "ALL".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

GSquared (9/28/2011)
select * from dbo.MyTable
order by case Users when 'ALL' then 1 else 2 end, Users ;
That avoids even things like entries that begin with low-ASCII-value punctuation coming before "ALL".

Nice idea. 😉