How to customize "Order By" in my select query?

Question

How to customize "Order By" in my select query?

halifaxdal

SSCoach

Points: 19741
More actions
July 6, 2009 at 1:52 pm

#131780

Hi folks,
I have a table with records like
1 aaaa John
2 bbbb Jane
3 aaaa John
4 bbbb John
5 aaaa Jane
6 bbbb John
7 aaaa Kim
8 bbbb John
What I want to do is to order my select query result in a certain order, for example, I might want Kim always appear on the top(or maybe Kim and then Jane), and then the rest ordered in alphabetic.
Can I do this? How do I do this? Thanks.

Viewing 15 posts - 1 through 14 (of 14 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You could add a SortPriority column, assign Kim a 1 and everyone else a 2, and then use that in your Order By. Would that work for what you need?

- 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

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Thanks for replying, I can't add extra column to the table, any other thought?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

July 6, 2009 at 2:50 pm

#1020666

Why do you want to do this?

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

Rather than add a SortOrder column to the table, just include one in your query e.g.

select id, forename, case when forename = 'Kim' then 1 else 2 end as SortOrder

from mytable

order by sortorder, forename

GSquared SSC Guru Points: 260824 More actions · Answer 5

Ian Scarlett (7/7/2009)
Rather than add a SortOrder column to the table, just include one in your query e.g.
select id, forename, case when forename = 'Kim' then 1 else 2 end as SortOrder
from mytable
order by sortorder, forename

If you do that, keep in mind that MS is planning on deprecating that ability in a future edition of SQL Server. It's a violation of the ANSI standard for Order By, apparently. At least, that's how I read that part of the deprecations web page.

- 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

halifaxdal SSCoach Points: 19741 More actions · Answer 6

Ian Scarlett (7/7/2009)
Rather than add a SortOrder column to the table, just include one in your query e.g.
select id, forename, case when forename = 'Kim' then 1 else 2 end as SortOrder
from mytable
order by sortorder, forename

Thank you Ian, it works amazingly.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

GSquared (7/7/2009)
Ian Scarlett (7/7/2009)
Rather than add a SortOrder column to the table, just include one in your query e.g.
select id, forename, case when forename = 'Kim' then 1 else 2 end as SortOrder
from mytable
order by sortorder, forename
If you do that, keep in mind that MS is planning on deprecating that ability in a future edition of SQL Server. It's a violation of the ANSI standard for Order By, apparently. At least, that's how I read that part of the deprecations web page.

Damn it... another useful thing is going to bite the dust. Screw ANSI.

Gus, can you post the URL for the deprecations web page, please? It's time someone at least tried to stop MS from killing everything useful and you posting that URL would save me a bit of time so I can load more pork chops for the boys in Redmond.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 8

Here's where I got that it might be deprecated: http://iablog.sybase.com/paulley/2009/01/more-transact-sql-features-bite-the-dust/

Here's the source for that: http://www.sqlmag.com/Articles/ArticleID/100697/pg/1/1.html (Subscription required)

But it's not on the official deprecations page, so far as I can find: http://msdn.microsoft.com/en-us/library/ms143729.aspx

So it may be more rumor than fact, but if Ben-Gan is the source of the rumor, and it's from Jan this year, I have to give it at least some credence.

- 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

terrance.steadman SSCrazy Points: 2097 More actions · Answer 9

Jeff Moden (7/7/2009)
GSquared (7/7/2009)
Ian Scarlett (7/7/2009)
Rather than add a SortOrder column to the table, just include one in your query e.g.
select id, forename, case when forename = 'Kim' then 1 else 2 end as SortOrder
from mytable
order by sortorder, forename
If you do that, keep in mind that MS is planning on deprecating that ability in a future edition of SQL Server. It's a violation of the ANSI standard for Order By, apparently. At least, that's how I read that part of the deprecations web page.

Here's where I got that it might be deprecated: http://iablog.sybase.com/paulley/2009/01/more-transact-sql-features-bite-the-dust/

Greetings, according to that article, it is ordering by an ordinal, not by a column name. The ordinal would basically be the numeric position of the column in the row. Some people may have used this in the past as a number is definitely a lot shorter than the name of the column, but it is also a lot easier to introduce hidden bugs. So, saying "ORDER BY SortOrder, ForeName" is fine, but saying "ORDER BY 3, 2" may not be accepted later.

Have a good day.

Terry Steadman

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

That's also what I get from the article. I never do an order by that uses ordinals like that, but I see no reason for them to remove it.

There are a lot of things I don't do that they're removing in the name of "ANSI". I don't see why they have to remove features to become "ANSI" compliant. I find "ANSI" only code to be limited compared to what the SQL Extensions in something like SQL Server 2000 and 2005 can do.

The new future requirement of using a ";" at the end of each T-SQL statement is, perhaps, my greatest disappointment. Let's stop and think about this... we're going to start getting errors that basically say "hey stupid... you forgot a semi-colon". My response will be, "hey stupid people who designed this crap... if you can figure that out, why do you require it and why are you trying to look like (ugh!) Oracle?" It's gonna cost me a lot of gas to drive that truckload of pork chops to the MS offices. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

GSquared (7/8/2009)
Here's where I got that it might be deprecated: http://iablog.sybase.com/paulley/2009/01/more-transact-sql-features-bite-the-dust/
Here's the source for that: http://www.sqlmag.com/Articles/ArticleID/100697/pg/1/1.html (Subscription required)
But it's not on the official deprecations page, so far as I can find: http://msdn.microsoft.com/en-us/library/ms143729.aspx
So it may be more rumor than fact, but if Ben-Gan is the source of the rumor, and it's from Jan this year, I have to give it at least some credence.

Thanks, Gus. I appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 12

halifaxdal:

Here is another option for producing the order you want without adding a column to the table or the output.

create table #temp

(num int,

cd varchar(5),

nm varchar(10))

set nocount on

insert #temp values (1,'aaaa','John')

insert #temp values (2,'bbbb','Jane')

insert #temp values (3,'aaaa','John')

insert #temp values (4,'bbbb','John')

insert #temp values (5,'aaaa','Jane')

insert #temp values (6,'bbbb','John')

insert #temp values (7,'aaaa','Kim')

insert #temp values (8,'bbbb','John')

select nm, num, cd from #temp

order by case when nm = 'Kim' then 1 else 2 end, nm, num

drop table #temp

nm num cd

---------- ----------- -----

Kim 7 aaaa

Jane 2 bbbb

Jane 5 aaaa

John 1 aaaa

John 3 aaaa

John 4 bbbb

John 6 bbbb

John 8 bbbb

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 13

halifaxdal:

Here is another option for producing the order you want without adding a column to the table or the output.

create table #temp

(num int,

cd varchar(5),

nm varchar(10))

set nocount on

insert #temp values (1,'aaaa','John')

insert #temp values (2,'bbbb','Jane')

insert #temp values (3,'aaaa','John')

insert #temp values (4,'bbbb','John')

insert #temp values (5,'aaaa','Jane')

insert #temp values (6,'bbbb','John')

insert #temp values (7,'aaaa','Kim')

insert #temp values (8,'bbbb','John')

select nm, num, cd from #temp

order by case when nm = 'Kim' then 1 else 2 end, nm, num

drop table #temp

nm num cd

---------- ----------- -----

Kim 7 aaaa

Jane 2 bbbb

Jane 5 aaaa

John 1 aaaa

John 3 aaaa

John 4 bbbb

John 6 bbbb

John 8 bbbb

mtassin SSC-Insane Points: 23099 More actions · Answer 14

Jeff Moden (7/8/2009)
The new future requirement of using a ";" at the end of each T-SQL statement is, perhaps, my greatest disappointment. Let's stop and think about this... we're going to start getting errors that basically say "hey stupid... you forgot a semi-colon". My response will be, "hey stupid people who designed this crap... if you can figure that out, why do you require it and why are you trying to look like (ugh!) Oracle?" It's gonna cost me a lot of gas to drive that truckload of pork chops to the MS offices. 😛

Perhaps we can contribute... last I checked SQL isn't C... keep the bloody semi-colons out of it when possible.

I look forward to the day when all VB code lines must end with a ;... or do they already do that in .net 4?

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]