Sql order by problem

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Hi all

SQL

pk_id    name 
1        bhagi
2        sumit
3        raju
4        ramesh
5        mac
6        hellen
7        allen
8        krishna
9        rashmi
10       radhamadhaba

This is my table.
I want to show all the data in this sequence

first 3 row pk_id = 1,2,10 than rest order by name.
means

SQL

pk_id    name
1        bhagi
2        sumit
10       radhamadhaba
rest order by name like
7        allen
6        hellen
etc...

In one query

Thanks in advance

Posted 29-Apr-12 21:31pm

bhagirathimfs

Add a Solution

4 solutions

Solution 1

Add a column for your exceptions (1,2,10) rows and set a sort order number in that column, then query as follows:

SQL

select * from [table] where [exception] > 0 order by [exceptions]
union 
select * from [table] where [exception] = 0 order by [name]

EDIT :

SQL

select * from [table] where pk_id in (1,2,10)
union
select * from [table] where pk_id not in (1,2,10) order by [name]

Posted 29-Apr-12 21:40pm

Mehdi Gholam

Updated 29-Apr-12 21:57pm

v2

Comments

bhagirathimfs 30-Apr-12 3:52am

Thanks for response but i can't add a column into that table.

I tried like this also

select * from [table] where pk_id in (1,2,10)
union
select * from [table] where pk_id not in (1,2,10) order by name

the above query is not doing order by for both the case
means this result is coming with out any sorting.

Mehdi Gholam 30-Apr-12 3:59am

Run the query in the management studio to check.

bhagirathimfs 30-Apr-12 5:15am

select * from [table] where pk_id in (1,2,10) order by pk_id
union
select * from [table] where pk_id not in (1,2,10) order by name

Solution 2

SQL

(select * from [table] where pk_id in (1,2,10))
union 
(select * from (SELECT * FROM [table] where pk_id not in (1,2,10) order by [name]) as somename )

Just try this code it should work.

Even pk_id not in (1,2,10) is not required because UNION will remove all duplicate entries.

Posted 30-Apr-12 0:12am

Loke.mysore

Updated 30-Apr-12 0:43am

v2

Solution 4

Well, you already got your answer but i would add an small suggestion, you might have those time when you would have to change the name list. what would you do then? you better add another column and give those three user least same value, and put some other value to rest of the user. you can call it user type, in that case your query complexity will be reduced too

Posted 30-Apr-12 13:23pm

Mohibur Rashid

Comments

bhagirathimfs 1-May-12 2:44am

Thanks.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bhagirathimfs · Accepted Answer · 2012-04-30T04:03:00

Solution 3

SQL

(select * from [table] where pk_id in (1,2,10)order by pk_id limit 1000)
union 
(select * from [table] order by name limit 1000)

Posted 30-Apr-12 4:03am

bhagirathimfs