I'll keep this as short as possible. I've read many of the posts re: order by in a view (searched here on "view with order by"). I know that TOP ### in order to support ORDER BY is a kludge, that might disappear in a future release.
However, a few quick questions/comments:
1) My front end is Access, the back end is SQL Server 2014 Express. I'd prefer the sorting done on the more powerful machine, rather than the end users' machines. However, the data is highly summarized by the view, so the data volumes aren't that bad if Access has to do the sorting. Is it best to get SQL Server to do the sorting and, if so, how if the source is a view?
2) I'd prefer to sort on a column I don't want to return in the view (StatusID). However, it's not a deal breaker to add it to the view and drop it from the select statement calling the view. Again, would like the BE to sort if possible, then I wouldn't need to include the extraneous column.
3) I'm unaware how I can use a stored procedure as a "virtual table" with an Access front end. Some Google hits suggested adding the sorting to a SP, but I don't think that works with Access. If Access can
use a SP as a data source, please let me know.
Thanks for pointing me in the right direction re: the above points.
P.S.: FWIW, I am used to ORDER BY being supported by views in other SQL dialects, so I wonder which standards, if any, are being honoured / violated by the different SQL dialects?
Here is the view source. It works but is frowned upon in the other hits on this forum.
CREATE VIEW view_Letters AS
SELECT TOP 999999999999
c.CaseNumber, u.Fullname as Analyst, m.CovNo, m.Suffix, m.LastName, m.FirstName, s.Status, t1.LetterDate, t1.Amount
SELECT CaseID, StatusID, LetterDate, sum(BenefitPaid) as Amount
WHERE Applicable = 1
GROUP BY CaseId, StatusID, LetterDate
LEFT JOIN fact_Cases c
LEFT JOIN dim_Users u
LEFT JOIN dim_Members m
LEFT JOIN dim_Status s
ORDER BY t1.StatusID, Amount DESC, LetterDate