ORDER BY using successful thousand comma separator

  • query

    This query requires ActualTotal to be ordered with largest value top however as you can see from the screengrab, this hasn't worked completely. Is there a way to achieve this? Here is my current code:

    SELECT m.name, FORMAT(x.ComTotal,'N0') AS Total_2020, 
    FORMAT(y.Totals,'N0') AS Some_2020,
    FORMAT(x.ComToTal + y.Totals,'N0') AS ActualTotal
    FROM Mees m
    LEFT JOIN View_2020_Listx_Jan2020 x
    ON m.name=x.m
    LEFT JOIN View_Listy_2020_Totals y
    ON m.name=y.name
    ORDER BY ActualTotal DESC;

    Any help much appreciated, thanks.

  • Change your ORDER BY clause to use numeric (rather than alphabetic) ordering:

    ORDER BY (x.ComToTal + y.Totals) DESC

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Your approach to SQL is completely wrong. Client/server architectures are based on tiers. One of these tears is a displayed level, which is where we put in commas and punctuation marks, and whatever else we need for display. SQL works in the database tier, so we don't put in punctuation marks, colors, font sizes, or any of that garbage. Since you're now working with strings, you're not getting the sorting you thought you get.

    SQL will come up with a standardized result set at and pass it to the next tier. The order by clause is a weird thing from a relational viewpoint. It converts a result set into a sequential file structure (cursor) and passes that new data structure to a display tier. Your mindset is still locked in an un-tiered, file system architecture like COBOL or maybe BASIC. It's also worth mentioning that the highly proprietary format () operator will eat up a lot of resources and slow everything down.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply