A Sorting Problem

Question

1.00/5 (1 vote)

See more:

Hi,
this is mahesh, iam having problem in sorting
My database Fields is PART_NO,slnoinpart,HOUSE_NO ,FM_NAME, ...etc

in that PART_NO ,i have 1 and 2 ,sort both Part_no 's House numbers Ascending order with All columns in both PART_NO

i am trying with Order by ASC ,HOUSE_NO shown like this order

1-10
1-10
1-100
1-100A

what exactly i want to do
1-10
1-10
1-11
1-11A
.
.
.
1-99
1-100

if chance to have more characters like - , \ , A ,B consider this also
No problem With Duplicate of HOUSE_NO

Plz any one Send Query As early as possible

Posted 25-Aug-10 0:39am

Maheshbabu.p

Updated 25-Aug-10 0:50am

v2

Add a Solution

Comments

Christian Graus 26-Aug-10 3:32am

Reason for my vote of 1
OP has created at least 4 accounts to ask this question over, and over, and over again.

2 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)

Toniyo Jackson · Answer 1 · 2010-08-25T01:10:00

Solution 1

the problem is ur HOUSE_NO field in database is string. so when u sort string u will the datas like this only.

Posted 25-Aug-10 1:10am

Toniyo Jackson

Comments

Maheshbabu.p 26-Aug-10 3:01am

iam written this query : select * from e-details order by house_no ASC

it displaying like this 1-1
1-1
1-1
1-10
1-10
1-100
1-100A
1-11

i want to display like this
1-1
1-1
1-10
1-10
1-11
after 1-99 only, i want 1-100

PART_NO slnoin
part HOUSE_NO FM_NAME
1 1 1 Chinnaraju
1 2 1 Kondaiah
1 638 1 Veeramma
1 641 1 Thirupathamma
1 637 1 Errapotaiah
1 639 1 Potaiah
1 634 1 BhiminiBai
1 635 1 Potamma
1 636 1 Chinnapoturaju
1 640 1 Potaiah
1 654 1-1 Guravamma
1 643 1-1 Bayamma
1 653 1-1 Naganna
1 642 1-1 Ankanna
1 10 1-1 Kondaiah
1 9 1-1 Anjaiah

1 560 1-1 Bayanna Seelam
1 549 1-1 Tikkala Ankaiah
1 52 1-10 Chevitibyanna
1 435 1-100 PothuRaju
1 436 1-100 Peddanna
1 437 1-100 Peddamma
1 434 1-100 Potaiah
2 269 1-100 Ankulu
2 270 1-100 Nagamma
2 271 1-100 Veeramma
2 272 1-100 RAJU
2 300 1-109 Nagamma
2 301 1-109 Nagamma
2 302 1-109 Mugemma
1 57 1-11 Potamma
1 60 1-11 Chinna
1 58 1-11 Anjayya
1 54 1-11 Galeiah
1 55 1-11 Basavamma
1 56 1-11 Bakkabyanna

Maheshbabu.p 26-Aug-10 3:12am

HOUSE_NO field in database is VARCHAR(40), order by sorting 1-10 ,1-100,1-100 ,1-11 this order , i want 1-10,1-11 ........1-99,1-100 this order , how it posible ?

#realJSOP 26-Aug-10 6:12am

DO NOT USE TEXT-SPEAK HERE.

Maheshbabu.p 26-Aug-10 8:48am

what's ur problem

Toniyo Jackson · Answer 2 · 2010-08-25T21:30:00

Solution 2

SQL

select *, cast(SUBSTRING (house_no,1,1) as decimal) as a, cast(SUBSTRING (house_no,3,3) as decimal) as b from e-details
order by a asc ,b asc

Posted 25-Aug-10 21:30pm

Toniyo Jackson