How to use order by while using joins

Question

0.00/5 (No votes)

See more:

SQL

select a.voucher_no,a.expensive_id,a.project_id,CONVERT(nvarchar(10), a.date, 103) as date,a.paid_to,b.expensive_name,a.credit,a.debit,a.s_type,a.particulars,a.amount
from tbl_a a inner join tbl_c on c.paidTo=a.paid_to
inner join tbl_b b on a.expensive_id=b.expensive_id  where 1=1 order by a.voucher_no ASC

The above query not in Ascending Order which is based on voucher_no number

Posted 15-Nov-15 22:14pm

Arasappan

Updated 15-Nov-15 22:15pm

v2

Add a Solution

Comments

Simon_Whale 16-Nov-15 4:20am

do you have an example of the output? also tell us what type voucher_no is

Arasappan 16-Nov-15 4:27am

voucher_no expensive_id
1008 101 1 22/06/2015
1008 101 1 22/06/2015
1009 58 1 22/06/2015
101 58 1 09/04/2015
1010 88 1 22/06/2015

Arasappan 16-Nov-15 4:28am

1009 then 101

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)

OriginalGriff · Accepted Answer · 2015-11-15T22:24:00

Solution 1

At a guess - but I've seen this so often it's not funny - it is in order, but your DB design is faulty.
I think you have voucher_no as a NVARCHAR or VARCHAR column, so the ORDER BY clause will use a string comparison instead of a numeric one. And in a string comparison, the ordering is done based on the first different character:

If you want a numeric order, then store numeric values in a numeric field, not string.

Posted 15-Nov-15 22:24pm

OriginalGriff

Comments

Arasappan 16-Nov-15 4:30am

ur genius griff..But The entries are already there how can I change it is there any chance like in c# int.parse

OriginalGriff 16-Nov-15 4:43am

You could...but it's a PITA because you will have to remember to do it each and every time you use it. And someone, somewhere is going to stick an non numeric character in there which will throw an exception in your (working) code - they may already have done it!

I'd strongly suggest that it's a lot less work in the long run to write a quick conversion program, which reads the table, converts appropriately with error checking and reporting, then writes a brand new table before deleting the original. It also give you a chance to correct any other columns which are the wrong type. It really is faster in the long run, especially since you are almost certainly at the early stages with this: once you start to get serious amounts of data in there it gets a lot, lot harder to fix...

But ... you can use

... ORDER BY CONVERT(INT, a.voucher_no) ASC

As a temporary "fix" - don't be too surprised if it throws an exception on some values already though! :laugh:

Arasappan 16-Nov-15 4:46am

order by cast(a.voucher_no as bigint) ASC

Is this Right

OriginalGriff 16-Nov-15 5:07am

It'll do the same as CONVERT.

But seriously - fix the underlying problem, not the symptom!

Arasappan 16-Nov-15 5:43am

where to add the order by in the dynamic sql

Abhinav S 16-Nov-15 4:33am

That's probably it. 5.