ordering range values in SQL

Question

0.00/5 (No votes)

See more:

i am currently having an issue on the following query.

The query runs fine and returns the correct data but my order by is messed up

it returns
0-50
101-200
201-300
301-500
1000
51-100

any idea why this could happen

this is my query

SQL

select
                case when amt >=0 and amt <50.00
                        then ' R0-R50'
                    when amt >=51.00 and amt <100.00
                        then 'R51-R100'
                    when amt >=101 and amt <200.00
                        then ' R101-R200'
                    when amt >=201 and amt <300.00
                        then ' R201-R300'
                    When amt >=301 and amt<501.00
                        then ' R301-R500'
                    When amt >=501 and amt < 1000.00
                        then ' R501-1000'
                            When amt >=1000
                        then '1000 and above'

                end [Range], count(*) [Number], sum(amt) [Total], round(avg(amt),2) [Average]
            from trxtable
            where status='Processed'
                and Program_ID = @ProgramID
                and amt > 0
                AND trxtable.Cd IN (SELECT Cd from Tblmem WHERE isnull(Comments, '') NOT LIKE 'TESTCARD')
                AND (CONVERT(CHAR(10),trxdate  ,120)) >= @startDate AND (CONVERT(CHAR(10),trxdate  ,120)) <= @endDate
            group by
                case when amt >=0 and amt <50.00
                        then ' R0-R50'
                    when amt >=51.00 and amt <100.00
                        then 'R51-R100'
                    when amt >=101 and amt <200.00
                        then ' R101-R200'
                    when amt >=201 and amt <300.00
                        then ' R201-R300'
                    When amt >=301 and amt<501.00
                        then ' R301-R500'
                    When amt >=501 and amt < 1000.00
                        then ' R501-1000'
                        When amt >=1000
                        then '1000 and above'
                end
            order by
                case when amt >=0 and amt <50.00
                        then ' R0-R50'
                    when amt >=51.00 and amt <100.00
                        then 'R51-R100'
                    when amt >=101 and amt <200.00
                        then ' R101-R200'
                    when amt >=201 and amt <300.00
                        then ' R201-R300'
                    When amt >=301 and amt<501.00
                        then ' R301-R500'
                    When amt >=501 and amt < 1000.00
                        then ' R501-1000'
                        When amt >=1000
                        then '1000 and above'
            end
    END

    SELECT @ErrorCode = 001
    SELECT @Errormessage='Successful'
END



GO

Posted 8-Oct-13 23:33pm

isi19

Add a Solution

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)

Madhu Nair · Answer 1 · 2013-10-09T00:58:00

Solution 1

Change the Order By Clause to

C#

ORDER BY Amt

Posted 9-Oct-13 0:58am

Madhu Nair

Mike Meinz · Answer 2 · 2013-10-09T02:00:00

Change the Order By clause to include a value that will cause the sort order you desire.

SQL

order by
    case when amt >=0 and amt <50.00
            then 1
        when amt >=51.00 and amt <100.00
            then 2
        when amt >=101 and amt <200.00
            then 3
        when amt >=201 and amt <300.00
            then 4
        When amt >=301 and amt<501.00
            then 5
        When amt >=501 and amt < 1000.00
            then 6
        When amt >=1000
            then 7
end

You might need to change the Group By, too