ORDER BY using successful thousand comma separator
-
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
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
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