Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ORDER BY in a view Expand / Collapse
Author
Message
Posted Thursday, July 10, 2014 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 6:45 PM
Points: 7, Visits: 55
Hi,

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
FROM (
SELECT CaseID, StatusID, LetterDate, sum(BenefitPaid) as Amount
FROM fact_Claims
WHERE Applicable = 1
GROUP BY CaseId, StatusID, LetterDate
) t1
LEFT JOIN fact_Cases c
ON t1.CaseID=c.CaseID
LEFT JOIN dim_Users u
ON c.AnalystID=u.UserID
LEFT JOIN dim_Members m
ON c.MemberID=m.MemberID
LEFT JOIN dim_Status s
ON t1.StatusID=s.StatusID
ORDER BY t1.StatusID, Amount DESC, LetterDate

Post #1591041
Posted Thursday, July 10, 2014 4:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 14,196, Visits: 28,520
I am not an Access person, but Access absolutely can call stored procedures within SQL Server and based on what you're describing, that's what you want, not a view. Or, you could try putting this into an inline table valued function. That can be called more or less just like a view. Just don't create a multi-statement table valued function. They generally perform very poorly.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1591076
Posted Monday, July 14, 2014 3:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 2,900, Visits: 3,298
I think that when you use an Access FE with a SQL Server BE, then Access will use 'pass-through' queries to get the data. The entire query text is executed by SQL Server, so any ORDER BY clause is run at the SQL Server BE, not by the Access FE.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1592107
Posted Monday, July 14, 2014 4:21 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 646, Visits: 2,992
...I know that TOP ### in order to support ORDER BY is a kludge, that might disappear in a future release.


I think I know what Post you are talking about.

TOP 100 PERCENT is not a kludge nor is TOP <really big number>. The TOP clause is supported through SQL Server 2014 and does not look like it's going to be depreciated anytime soon. Microsoft usually warns you when a feature is going to be depreciated.

TOP is pretty major and affects query processing; stuff like that is not depreciated very often. Take a look at this query processing order pdf:
Logic Query Processing. The stuff you see here is likely not going away for a long, long time (e.g. SELECT, APPLY, JOIN, GROUP BY, WHERE, HAVING, DISTINCT, TOP, SELECT, ETC...)

They kind of stuff that gets depreciated looks more like this. This is purely opinion based on my observations.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1592378
Posted Tuesday, July 15, 2014 1:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Alan.B (7/14/2014)
TOP 100 PERCENT is not a kludge nor is TOP <really big number>.


It is kinda. TOP (100) PERCENT ... ORDER BY will result in SQL ignoring the order by in a view because it's not a row-limiting top. Whether the optimiser will get smart enough to realise that TOP (really large number) is also not a row limiting top and hence will allow it to ignore the order by is an open question. It's probably not something that will be indicated as deprecated if it ever happens, because it's used to evade the SQL rule that only and ORDER BY on the outer-most select has any effect on the ordering.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592422
Posted Tuesday, July 15, 2014 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 6:45 PM
Points: 7, Visits: 55
I'm used to being able to specify an order by in a view in other dialects of SQL. I find it handy.

Rather than Microsoft deprecating (or silently removing) TOP <big number> in the future, it would be nice if they would officially support an order by in a view.

Is there a particular SQL standard that dictates that order by cannot be specified in a view?

Re: my original post: I have defined the summarisation view w/o an order by, and a simple stored procedure selecting the view including an order by. I've researched Access <--> SQL Server more, and Access can use the SP as a (readonly) record source, so that will meet my needs. In this instance, it's fine that the record source is readonly in Access.

Thanks all for your replies.
Post #1592571
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse