problem with order by query in sql

Question

0.00/5 (No votes)

See more:

hi friend i have table
columns name is carton
output is comming such as:when i given order by carton asc

VB

1-11
1-14
1-5
15-19
20-30
2-2
31-31
5-4
5-4
5-4
5-4
5-4
6-5

i want to these records
order by
like
1-5
1-14
2-2

i'll try order by asc statement but it show these value that is given
but there is no effect on record
because these record pick 1st value
thats the problem
plz help

Posted 11-May-12 23:22pm

Kishanthakur

Add a Solution

3 solutions

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 · 2012-05-11T23:29:00

Solution 1

If the type of [carton] field is text, ORDER BY clause[^] treats it as a text, not numbers.

You need to Split your data by '-'. How?

SQL

SELECT  [carton], CONVERT(INT,SUBSTRING([carton], 1, CHARINDEX('-',[carton])-1)) AS [c1], 
		CONVERT(INT,SUBSTRING([carton], CHARINDEX('-',[carton])+1 , LEN([carton])-CHARINDEX('-',[carton])+1)) AS [c2]
FROM YOUR_TABLE_NAME
ORDER BY [c1], [c2], [carton]

Posted 11-May-12 23:29pm

Maciej Los

Updated 11-May-12 23:56pm

v2

Comments

Wendelius 12-May-12 5:49am

Exactly, treated as text!

Maciej Los 12-May-12 5:56am

Thank you ;)

Kishanthakur 12-May-12 6:43am

thank you boss your ans solved my problems
thank u so much....

Sandeep Mewara 12-May-12 5:50am

Good answer. 5!

Maciej Los 12-May-12 5:56am

Thank you ;)

VJ Reddy 12-May-12 6:03am

Good answer. 5!

Maciej Los 12-May-12 6:08am

Thank you ;)

P.Salini 12-May-12 7:20am

My +5

Maciej Los 12-May-12 8:21am

Thank you ;)

Wendelius · Accepted Answer · 2012-05-11T23:47:00

Solution 2

In my opinion the best solution would be to break the information into two separate columns. The first number in one column and the second number in another column.

I'm suggesting this because in the database you should never concatenate different data into a single column. You can always format the data when it's displayed.

If you need to use the current data structure, you can split the elements in the order by clause. Something like:

SQL

...
ORDER BY CAST( SUBSTRING( fieldname, 1, CHARINDEX( fieldname, '-')) AS int),
         CAST( SUBSTRING( fieldname, CHARINDEX( fieldname, '-'), 9999) AS int)

Posted 11-May-12 23:47pm

Wendelius

Updated 12-May-12 0:56am

v2

Comments

Sandeep Mewara 12-May-12 5:50am

Good suggestion 5!

Wendelius 12-May-12 6:02am

Thanks :)

Maciej Los 12-May-12 5:57am

Good work! My 5!

Wendelius 12-May-12 6:02am

Thanks :)

VJ Reddy 12-May-12 6:03am

Good answer. 5!

Wendelius 12-May-12 6:06am

Thans VJ :)

P.Salini 12-May-12 7:21am

My +5

Wendelius 12-May-12 7:24am

Thanks :)

VJ Reddy · Accepted Answer · 2012-05-11T23:57:00

If the data is to be ordered using C# with LINQ then following procedure can be used

C#

//If the above data is in cartonData DataTable in the Carton Column

var orderedCartonData = cartonData.AsEnumerable()
		.OrderBy (d => d.Field<string>("Carton").Split('-')
		.Aggregate ("", (x,y) => x.PadLeft(5,'0') + y.PadLeft(5,'0')));
//If the data is in a List then
List<string> data = new List<string>(){
	"1-11","1-14","1-5","15-19","20-30","2-2",
	"31-31","5-4","5-4","5-4","5-4","5-4","6-5"	};
var orderedData = data.OrderBy (d => d.Split('-')
	.Aggregate ("",(x,y) => x.PadLeft(5,'0')+y.PadLeft(5,'0')));

//The contents of orderedCartonData
//1-5 
//1-11 
//1-14 
//2-2 
//5-4 
//5-4 
//5-4 
//5-4 
//5-4 
//6-5 
//15-19 
//20-30 
//31-31

The main point here is that, to Sort the items as 1-5, 1-14, 2-2, even if we split these in to two separate data items, still 14 will come first before 5 when sorted as text. To overcome this the data can be prefixed with 0s like 0000100005, 0000100014, 0000200002 as shown in the above query.