How to order fields in sql server in select query ?

Question

0.00/5 (No votes)

See more:

Hai
i need to order fields in sql serve in select query,then only display in ascending order,Actually i will store treeview path in database,based on parent,sub root again 2nd sub root and so on.i have 4 levels and in 5th i store file.

SQL

 ID      Path             User   Loc    Name      Parent       Voltage
3342	240kV	          Admin	 root	132kV	Relay Manuals  240kV
3343	240kV\E02+R	  Admin	 Sub 1	E02+R	  240kV\       240kV
3344	240kV\E02+R\P123  Admin	 Sub 2	P123	  E02+R\       240kV
3345	240kV\E08+W	  Admin	 Sub 1	E08+W	  240kV\       240kV
3346	240kV\E08+W\P921  Admin	 Sub 2	P921	  E08+W\       240kV
3348	240kV\E03+W	  Admin	 Sub 1	E03+W	  240kV\      240kV 'need to go after E02+R\P123
3349	240kV\E03+W\P921  Admin	 Sub 2	P921	  E03+W\      240kV 'Need to after E03+W

But my need is i need to order path like this way

SQL

 ID      Path             User   Loc    Name      Parent        Voltage
3342	240kV	          Admin	 root	132kV	Relay Manuals	 240kV
3343	240kV\E02+R	  Admin	 Sub 1	E02+R	  240kV\	 240kV
3344	240kV\E02+R\P123  Admin	 Sub 2	P123	  E02+R\	 240kV 'like this
3348	240kV\E03+W	  Admin	 Sub 1	E03+W	  240kV\	 240kV 'like this
3349	240kV\E03+W\P921  Admin	 Sub 2	P921	  E03+W\	 240kV
3345	240kV\E08+W	  Admin	 Sub 1	E08+W	  240kV\	 240kV
3346	240kV\E08+W\P921  Admin	 Sub 2	P921	  E08+W\	 240kV

Posted 23-Mar-14 18:24pm

Aravindba

Add a Solution

2 solutions

Solution 2

my suggestion is to set primary key to the column path.......because with the help of primary key the column is sorted in asc order...

Posted 23-Mar-14 20:11pm

Pranav-BiTwiser

Comments

Peter Leow 24-Mar-14 2:27am

Seeing is believing: http://sqlfiddle.com/#!3/2d464/1
more reference: http://www.w3schools.com/sql/sql_orderby.asp

Aravindba 24-Mar-14 3:30am

thanks for ur reply,actually already i use primary key and order by ascending order also,but what problem in my above question u can see second sql datas,in that id is changed 3342,3343,3344,3348,3349,3345 and 3346 this is my need,if i use asc order in id it not work

Pranav-BiTwiser 24-Mar-14 3:36am

u can try order by path asc....suggested by peter...

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)

Peter Leow · Accepted Answer · 2014-03-23T19:00:00

Solution 1

Try this:

SELECT * FROM table1 ORDER BY path ASC

Posted 23-Mar-14 19:00pm

Peter Leow

Comments

Peter Leow 24-Mar-14 2:25am

Seeing is believing: http://sqlfiddle.com/#!3/2d464/1
more reference: http://www.w3schools.com/sql/sql_orderby.asp

Pranav-BiTwiser 24-Mar-14 2:42am

you are right....by mistake i wrote that....
more than seeing and believing..practically doing is most important....:)

Peter Leow 24-Mar-14 2:45am

It is alright, we are always learning from one another. keep up the spirit.

Pranav-BiTwiser 24-Mar-14 3:06am

yes....thank you..:)

Pranav-BiTwiser 24-Mar-14 3:11am

@Peter-- is this ur profile??
https://www.facebook.com/peter.leow.16?fref=ts

Peter Leow 24-Mar-14 3:13am

No.

Pranav-BiTwiser 24-Mar-14 3:22am

u dont have facebook account?? if u have tell me....i will send u friend request

Aravindba 24-Mar-14 3:35am

5+