How to change the column position in select query

Question

0.00/5 (No votes)

See more:

I have a table named 'TITLE' .Table structure is like this

TITLE_ID || TITLE_NAME
========================
1 || Herr
2 || Frau
3 || Kind
4 || Fam

What I have tried:

I wrote a query for selecting the data ,"SELECT TITLE_ID,TITLE_NAME FROM TITLE ORDER BY 'TITLE_ID' asc" , got the result like this,

TITLE_ID || TITLE_NAME
========================
1 || Herr
2 || Frau
3 || Kind
4 || Fam

But I want " 2 || Frau " in first position and " 1 || Herr " in second position . Is it possible by writing the select query ?

Posted 21-Feb-16 19:13pm

vinu paul

Updated 21-Feb-16 21:03pm

Add a Solution

Comments

dan!sh 22-Feb-16 1:23am

On what basis do you want to order the data?

vinu paul 22-Feb-16 1:47am

Ascending order , but need " 2 || Frau " in first position and " 1 || Herr " in second position

dan!sh 22-Feb-16 2:10am

Ascending order of what? Name? Id? Something else?

vinu paul 22-Feb-16 2:18am

Ascending order of id

dan!sh 22-Feb-16 2:26am

You understand 1 < 2. Right? So record with id 1 WILL come before 2.

vinu paul 22-Feb-16 2:55am

ya i know 1 < 2 ,but i need 2 first ..I avoid the order of id or name. Now there is no order ,only i want " 2 || Frau" in first position

dan!sh 22-Feb-16 3:02am

Why? See, it is not a big deal to get this done but I don't see why would you need to do this. You are missing something here. If there is a reason why a certain record be ordered in certain way, that reason must be saved in database.

vinu paul 22-Feb-16 3:12am

Thats the requirement,in my project some forms need that order and some forms need normal order,If I make any change in DB,there will be a huge crash ,thats why.
Thanks Danish

2 solutions

Solution 1

You are ordering records from the "TITLE_ID" field, You should order query by "TITLE_NAME" like that:

SELECT TITLE_ID,TITLE_NAME FROM TITLE ORDER BY TITLE_NAME

This query will return result as

TITLE_ID || TITLE_NAME
========================
1 || Fam
2 || Frau
3 || Herr
4 || Kind

Ashish Nigam

Posted 21-Feb-16 19:40pm

Nigam,Ashish

Updated 21-Feb-16 19:44pm

v2

Comments

vinu paul 22-Feb-16 1:49am

I tried this,but in this query "1 || Fam" come first position and again "2 || Frau" in second position

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)

Maciej Los · Accepted Answer · 2016-02-21T21:03:00

Solution 2

Have you tried to use ORDER BY + CASE WHEN ...?

SQL

SELECT ...
FROM ...
ORDER BY CASE TITLE_ID WHEN 2 THEN 0 ELSE TITLE_ID END

See:
ORDER BY CASE | SQL with Manoj[^]
SQL SERVER - CASE Statement in ORDER BY Clause - ORDER BY using Variable - Journey to SQL Authority with Pinal Dave[^]

Posted 21-Feb-16 21:03pm

Maciej Los

Comments

vinu paul 22-Feb-16 3:29am

Now i tried this.. got the result..

I tried another way,will also get the result
SELECT TITLE_ID,TITLE_NAME FROM TITLE WHERE TITLE_ID =2
union all

SELECT TITLE_ID,TITLE_NAME FROM TITLE WHERE TITLE_ID !=2

Thanks :)

Maciej Los 22-Feb-16 3:45am

Hmmm... Your approach is wrong in performance aspect. As you can see, you have gto 2 select statements and it causes that database is queried twice. Got it?

vinu paul 22-Feb-16 3:52am

Ya got it. Thank you very much for your valuable informations :)

Maciej Los 22-Feb-16 3:56am

You're very welcome.
Cheers,
Maciej