How to order by in sql like this..

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have a table and its value like this

Area    subArea         ID       Ac
--------------------------------------
ARRAH	ARA URBAN-1	001	9027
ARRAH	ARA URBAN-1	001	E-2399
ARRAH	ARA URBAN-1	003	E-2402
ARRAH	ARA URBAN-1	003	E-2403
ARRAH	ARA URBAN-1	003	E-3289
ARRAH	ARA URBAN-1	001	E-2936
ARRAH	ARA URBAN-1	001	E-2937
ARRAH	ARA URBAN-1	001	E-2794
ARRAH	ARA URBAN-1	005	E-2803
ARRAH	ARA URBAN-1	005	H-1173
ARRAH	ARA URBAN-1	005	H-1174
ARRAH	ARA URBAN-1	005	H-02660
ARRAH	ARA URBAN-1	001	H-02591
ARRAH	ARA URBAN-1	001	H-02595
ARRAH	ARA URBAN-1	001	H-05465
ARRAH	ARA URBAN-1	002	H-03756
ARRAH	ARA URBAN-1	002	H-5482

I want order it by Area,subArea,ID,Ac.
i want mainly order it by Ac like this

9027
E-2399
E-2402
E-2403
E-2794
E-2803
E-2936
E-2937
E-3289
H-1173
H-1174
H-02591
H-02595
H-02660
H-03756
H-05465
H-5482

please help me..
Thanks..

[edit]Code block added - OriginalGriff[/edit]

Posted 25-Sep-13 20:39pm

krrazyumesh

Updated 25-Sep-13 20:41pm

OriginalGriff

v2

Add a Solution

Comments

krrazyumesh 26-Sep-13 2:49am

when i write a querry in sql like

select Area,subArea,ID,AC from mytable order by Area,subArea,ID,AC

then i want Ac column output like this..

Ac
---
9027
E-2399
E-2402
E-2403
E-2794
E-2803
E-2936
E-2937
E-3289
H-1173
H-1174
H-02591
H-02595
H-02660
H-03756
H-05465
H-5482

3 solutions

Solution 1

I guess this what you want to do. fields and select are independent.

SQL

select Area,subArea,ID,AC from mytable order by AC

Posted 25-Sep-13 20:54pm

ArunRajendra

Comments

krrazyumesh 26-Sep-13 2:58am

u r ri8 but my out put is not according to me like

9027
E-2399
E-2402
E-2403
E-2794
E-2803
E-2936
E-2937
E-3289
H-1173
H-1174
H-02591
H-02595
H-02660
H-03756
H-05465
H-5482

ArunRajendra 26-Sep-13 3:02am

Ok, Post whats the order you are getting.

OriginalGriff 26-Sep-13 3:05am

Reason for my vote of one: please read the question: he wants to order the values according to the numeric value of a text field - that needs a little special care!

ArunRajendra 26-Sep-13 3:12am

Ok. Was too quick to post.

Solution 3

SELECT * FROM TABLE Order By AC

Posted 25-Sep-13 21:40pm

VeeraAnnapureddy

Comments

OriginalGriff 26-Sep-13 3:52am

Reason for my vote of one: Read the question before answering.
This looks like a blatant attempt to "harvest" reputation points, by providing any answer, regardless of if it helps anyone.

Should I look at your other answers? Maybe I should, in an hour or so, when I have time...

krrazyumesh 26-Sep-13 4:04am

Thanks OriginalGriff

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)

OriginalGriff · Accepted Answer · 2013-09-25T21:04:00

Solution 2

This is a little complex, because you are trying to order a string value by a numeric value - so what you need to do is cast the numeric part as an integer:

SQL

SELECT * FROM MyTable
ORDER BY
(CASE WHEN SUBSTRING(Ac, 1, 1) LIKE '[0-9]'
         THEN CAST(Ac AS INT)
      ELSE CAST(SUBSTRING(Ac, 3, 999) AS INT) + 1000000
 END)

The 1000000 add-on is to move all the "E-" and "H-" values far beyond the "numeric only" values.

You may want to change it slightly to use the first character of the AC field to modify the 1000000 offset if you also want that included in the ordering, or provide two Order clauses.

Posted 25-Sep-13 21:04pm

OriginalGriff

Updated 25-Sep-13 21:07pm

v2

Comments

krrazyumesh 26-Sep-13 3:32am

Thanks OriginalGriff...

But i get some problem..
in my database the value of Ac is T-2790
when i apply your solution error occurs the error is
Conversion failed when converting the varchar value 'T-2790' to data type int.
same with I-6342

and i have Ac values also like wdi-9873,dh-6273,D08936 and so on

please help me..

OriginalGriff 26-Sep-13 3:50am

If T-2790 failed, then you typed rteh code wrong! :laugh: (I checked it here with alphachar-number and it works fine).
If your field is that variable, then you need to look at a much, much more complex ordering: it's not trivial to work out directly what you should do.
You need to sit down and look at what data you have and what order you want them in: it's pretty easy when its A-0000 to Z-999999 to order them by "Alpha character then by number within Alpha group", but is you also have "abc0000" and "abc-0000" and "ab-0000" and so on, then you need to decide exactly what the ordering should be before you go any further!
For example, is the order A-0000, AB-0000, B0000, B-0000,... or A-0000, B-0000, B0000, AB-0000,...? Is case important?

If this gets too complex, it would probably be worth either moving the sort criteria to C# where processing this stuff is a whole lot easier, or writing an SQL user function and hoping like heck you never have to change it...:laugh:

krrazyumesh 26-Sep-13 4:22am

Thanks OriginalGriff ...

OriginalGriff, for example i have A-123,A124,A122,A0125,A0120,AHT126,AHT-119,A-126,
AHT-0127,AHT-128

same for B and so on..
I want after ordering it will be like A0120, A122, A-123, A124, A0125, A-126, AHT-119, AHT126,AHT-0127,AHT-128....

OriginalGriff 26-Sep-13 4:35am

I thought it might be something like that - That's a PITA to do in SQL, and probably rather slow for each row in and ORDER BY since the actual processing will be done multiple times as the items are compared a couple of times to get the right order.

I'd move the sort into C#, where a Regex can do the bulk of the work, followed by a quick Linq OrderBy and a custom Comparison method. It's the same work, but it's a lot easier to code and understand than the SQL alternative - and it shares the load across your users instead of "blocking" the SQL server if you have a lot of rows.

krrazyumesh 26-Sep-13 4:41am

what is PITA ?..
i m not understanding what u say...

OriginalGriff 26-Sep-13 5:00am

PITA == "Pain In The A***"
A common acronym for something that's a real annoyance to do.
(Also a Mediterranean flat bread, but that's not relevant here ;) )