order by my wish in sql server..

Question

1.00/5 (1 vote)

See more:

I was trying to order by my wish. But i m unable to do.

SQL

select * from #tempmonthly with(nolock) order by hub_id(sorted,12,1,20,2,136,3,9,4,1,5,652,6,672,7,129,8,240,9,17,10,14,11,627,12,18,13,574,14,158,15,145,16)

I tried this, but i got error
'hub_id' is not a recognized built-in function name.
Working code is as follows:

SQL

select * from #tempmonthly with(nolock) order by hub_id

Guide me[as i want to arrange by my own order by].

I tried to google it. I got this answer like this

C#

order by decode( sorted, 'F',1, 'C',2, 'A',3, 'B',4, 'G',5, 'D',6, 'E',7, 8 )

So i tried to do that in my code:

C#

select * from #tempmonthly with(nolock) order by hub_id (sorted,12,1,20,2,136,3,9,4,1,5,652,6,672,7,129,8,240,9,17,10,14,11,627,12,18,13,574,14,158,15,145,16)

But it is not working

Posted 20-Aug-13 2:16am

anurag19289

Updated 20-Aug-13 2:48am

Thanks7872

v5

Add a Solution

Comments

Joezer BH 20-Aug-13 8:22am

Looks like your SQL statements are identical, am I missing something?

anurag19289 20-Aug-13 8:31am

question is improved...

ZurdoDev 20-Aug-13 8:24am

The error is clear. It does not know what hub_id is. However, unless I am blind your 2 SQL statements are the same.

anurag19289 20-Aug-13 8:26am

thanks for your comment.. let me clear it..

query 1
select * from #tempmonthly with(nolock) order by hub_id (sorted,12,1,20,2,136,3,9,4,1,5,652,6,672,7,129,8,240,9,17,10,14,11,627,12,18,13,574,14,158,15,145,16)

query 2
select * from #tempmonthly with(nolock) order by hub_id

anurag19289 20-Aug-13 8:28am

i tried to google it ,,, i got that sorted part...so immediatey after order by (sorted,)

this starts... but this is not working

Thanks7872 20-Aug-13 8:34am

Is there anything wrong with you anurag? Why you are so desperate to have a prompt resolution? Let some one have look at this and understand the problem. Your explanation is not at all clear in question and comment also.

You have written two identical query. With one you say error and with the same,you said it works fine.
In comment,you said this starts,but this is not working? What do you mean by this?

We,here,only want the problematic part. If you are here to have a resolution than you must edit your question in order to make it more like a question.

anurag19289 20-Aug-13 9:44am

Hi Rohan-

The two querries were not identical. I think i should have elaborated it more clearly.

select * from #tempmonthly with(nolock) order by hub_id desc

Rohan- i have the hub id as
12
20
136
9
1
652
and so on.

but if i do order by hub_id desc
i will get output like

652
136
20
12
9
1
in this way...

but actually i wanted in this way : 12 ,20,136,9,1,652

so i searched google for this. from one article i got a solution like

order by decode( sorted, 'F',1, 'C',2, 'A',3, 'B',4, 'G',5, 'D',6, 'E',7, 8 )--he was trying to order the letters as per his wish

--so i thought i can use this

select * from #tempmonthly with(nolock) order by hub_id (sorted,12,1,20,2,136,3,9,4,1,5,652,6)

but i was unable to do....

ZurdoDev 20-Aug-13 8:38am

I think I might actually understand you now. Do you actually have the (sorted,12,1...) in your SQL statement? If so, why? What are you trying to do?

Thanks7872 20-Aug-13 8:49am

Yes, I am sure that is what the OP wants to convey. Please have a look at updated question.

ZurdoDev 20-Aug-13 8:52am

I don't think Microsoft SQL Server supports this, http://technet.microsoft.com/en-us/library/ms188385.aspx What exactly do you want?

1 solution

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 · 2013-08-20T03:10:00

There is no other way than creating new table ;)

SQL

DECLARE @sortOrder(hub_id INT, ord INT IDENTITY(1,1))

INSERT INTO @sortOrder (hub_id)
SELECT 12
UNION ALL SELECT 1
UNION ALL SELECT 20
UNION ALL SELECT 2
UNION ALL SELECT 136
UNION ALL SELECT 3
UNION ALL SELECT 9
UNION ALL SELECT 4
UNION ALL SELECT 1
UNION ALL SELECT 5
UNION ALL SELECT 652
UNION ALL SELECT 6
UNION ALL SELECT 672
UNION ALL SELECT 7
UNION ALL SELECT 129
UNION ALL SELECT 8
UNION ALL SELECT 240
UNION ALL SELECT 9
UNION ALL SELECT 17
UNION ALL SELECT 10
UNION ALL SELECT 14
UNION ALL SELECT 11
UNION ALL SELECT 627
UNION ALL SELECT 12
UNION ALL SELECT 18
UNION ALL SELECT 13
UNION ALL SELECT 574
UNION ALL SELECT 14
UNION ALL SELECT 158
UNION ALL SELECT 15
UNION ALL SELECT 145
UNION ALL SELECT 16

then to join both tables:

SQL

SELECT t1.*
FROM #tempmonthly AS t1 INNER JOIN @sortOrder AS t2 ON t1.hub_id = t2.hub_id
ORDER BY t2.ord

Another way is to use CASE with ORDER statement, but - in my opinion - this is less elegant ;(

SQL

SELECT *
FROM #tempmonthly
ORDER BY CASE WHEN hub_id = 12 THEN 1
    WHEN hub_id = 1 THEN 2
    WHEN hub_id = 20 THEN 3
    WHEN hub_id = 2 THEN 4
    ...
    ELSE 123 END