Sort SQL output results by a column not in SELECT

Question

I have created a SQL query that works perfect for fetching the data needed. However I can not get it to Order by a column that is not present in the SELECT section. I found a similar question that was answered, but could not understand exactly what it was doing.

Example

I'm trying to get the whole output to be sorted by 'CustomerLastName' but not have the that column show in the output file.

I have:

SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), LEN(CustomerNumber)),'D','CAFET','', sum(Total) as totalsales
FROM ViewDetailedSalesReport
WHERE DateSold BETWEEN ('20200101') AND DateADD(day,1,'20200131')
GROUP BY CustomerNumber
ORDER BY CustomerLastName ASC

I know from reading other articles that I can not Order by something that is not in the SELECT portion, but can't quite understand how to use a 'non-declared' column to order by.

"I know from reading other articles that I can not Order by something that is not in the SELECT portion" That isn't quite true. You can sort by columns that aren't in the SELECT, but in this case CustomerLastName isn't in your GROUP BY so it can't be. You just need to add CustomerLastName to the GROUP BY clause. as I assume CustomerNumber is unique to a customer. — Thom A
– Thom A ♦, Commented Feb 10, 2020 at 19:38
Yes the CustomerNumber is unique, however the CustomerLastName is not, I was under the impression that the GROUP BY would be how the data is totaled together, thus I wouldn't want a non-uniqe field in that section? So if I understand you correctly I should be able to use GROUP BY CustomerNumber, CustomerLastName ORDER BY CustomerLastName ASC Is that how it would look? — Tohny.Johnson
– Tohny.Johnson, Commented Feb 10, 2020 at 19:57
I would expect so, yes (I can't see your data, so impossible for test). — Thom A
– Thom A ♦, Commented Feb 10, 2020 at 19:59
And if a column is more unique than another, the. Grouping by the less unique won't change the aggregation. In this case CustomerLastName is going to be a consistent value for a given CustomerNumber, so won't change the end results. — Thom A
– Thom A ♦, Commented Feb 10, 2020 at 20:00
That worked perfectly and was much simpler than I expected! Thank you for your help! — Tohny.Johnson
– Tohny.Johnson, Commented Feb 10, 2020 at 20:05

Tohny.Johnson · Accepted Answer · 2020-02-10 20:10:56Z

1

EDIT: Thank you @Larnu for letting me know it was a simple fix by adding my CustomerLastName column to the Group By section. It now works exactly as expected.

SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), LEN(CustomerNumber)),'D','CAFET','', sum(Total) as totalsales
FROM ViewDetailedSalesReport
WHERE DateSold BETWEEN ('20200101') AND DateADD(day,1,'20200131')
GROUP BY CustomerNumber, CustomerLastName
ORDER BY CustomerLastName ASC

answered Feb 10, 2020 at 20:10

Tohny.Johnson

678 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-02-10 20:16:28Z

1

Of course you can order by something not in the SELECT. The issue is the GROUP BY. If you are not ordering by a GROUP BY key (or a column alias defined in the SELECT), then you need an aggregation function:

SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), LEN(CustomerNumber)),
       'D', 'CAFET', '',
       sum(Total) as totalsales
FROM ViewDetailedSalesReport
WHERE DateSold BETWEEN ('20200101') AND DateADD(day,1,'20200131')
GROUP BY CustomerNumber
ORDER BY MAX(CustomerLastName) ASC

answered Feb 10, 2020 at 20:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Sort SQL output results by a column not in SELECT

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related