SELECT question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT question

Author

Topic

acbarberi
Starting Member

11 Posts

Posted - 2010-02-16 : 19:41:45

I need to create a SELECT statement of some sort that will select all the records from the table, but put a specific one first.

Example:
The table has two fields: ID and Name.
The table has this data:
1, Alfred
2, Casey
3, John
4, Corey

I want it to show the person with ID 2 first (Casey), and then show the rest in alphabetical order so that my list would come out like so:
2, Casey
1, Alfred
4, Corey
3, John

How can I do this?

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-16 : 19:50:14

ORDER BY CASE....

Isn't that pretty arbitrary?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

acbarberi
Starting Member

11 Posts

Posted - 2010-02-16 : 19:53:55

Thanks man! I'm coming from Oracle and I've never used ORDER BY CASE before.

What I'm doing is having the user select a particular ID, and when they do it puts that ID in the list first and then sorts all the rest in alphabetical order.

Now I'll go look up how to use ORDER BY CASE.

acbarberi
Starting Member

11 Posts

Posted - 2010-02-16 : 20:02:07

How about this code?

select * from people where id = 2
union
select * from people
order by case when id != 2 then firstName end

Will that accomplish what I'm trying to do? It gives me an error when I try that code.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-02-16 : 20:22:10

[code]select * from people
order by
case when id = 2 then 0 -- 0 is an id less than all other possible values of id.
else id end[/code]

acbarberi
Starting Member

11 Posts

Posted - 2010-02-16 : 20:23:06

Thanks! :D

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 01:43:30

quote:

Originally posted by sunitabeck

select * from people
order by
   case when id = 2 then 0  -- 0 is an id less than all other possible values of id.
   else id end


select * from people
order by
   case when id = 2 then 0  
   else 1 end,name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources