March 15, 2017 at 12:29 pm
In a t-sql 2012 view, I would like to sort the data by mailAddress. However a sort is not allowed in a view. Thus I am trying to determine an alternative. Here is what I have come up with so far:
CREATE VIEW dbo.vDirectCeASselect * from dbo.DirectCe where mailAddress = space(1) or city = space(1) or state = space(1) or zip=space(1)UNION ALLselect * from dbo.DirectCe where (mailAddress <> space(1)) and (city <> space(1)) and (state <> space(1)) and (zip<>space(1))GO
Would you show no other options on what I can do to solve my problem?
March 15, 2017 at 12:40 pm
wendy elizabeth - Wednesday, March 15, 2017 12:29 PMIn a t-sql 2012 view, I would like to sort the data by mailAddress. However a sort is not allowed in a view. Thus I am trying to determine an alternative. Here is what I have come up with so far:
CREATE VIEW dbo.vDirectCeASselect * from dbo.DirectCe where mailAddress = space(1) or city = space(1) or state = space(1) or zip=space(1)UNION ALLselect * from dbo.DirectCe where (mailAddress <> space(1)) and (city <> space(1)) and (state <> space(1)) and (zip<>space(1))GOWould you show no other options on what I can do to solve my problem?
Views have no order. The order must be defined when you query the view if needed.
March 15, 2017 at 10:31 pm
Can't you do this? If you use TOP 100 PERCENT, you can use ORDER BY.
CREATE VIEW vwFirstInstances
AS
SELECT * FROM (
SELECT TOP 100 PERCENT
PatientID,
SymptomName,
Severity,
ConsentDate,
ROW_NUMBER() OVER (PARTITION BY PatientID, SymptomName ORDER BY ConsentDate) AS rn
FROM dbo.Diagnoses
ORDER BY PatientID, SymptomName, ConsentDate) x;
March 16, 2017 at 3:22 am
pietlinden - Wednesday, March 15, 2017 10:31 PMCan't you do this? If you use TOP 100 PERCENT, you can use ORDER BY.
CREATE VIEW vwFirstInstances
AS
SELECT * FROM (
SELECT TOP 100 PERCENT
PatientID,
SymptomName,
Severity,
ConsentDate,
ROW_NUMBER() OVER (PARTITION BY PatientID, SymptomName ORDER BY ConsentDate) AS rn
FROM dbo.Diagnoses
ORDER BY PatientID, SymptomName, ConsentDate) x;
My understanding is that your example will not Guarantee Order, see this link TOP 100 Percent ORDER BY Considered Harmful
The article suggests that you would need to do the TOP 100 PERCENT on the outer select for it to be Guaranteed.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 16, 2017 at 5:25 am
pietlinden - Wednesday, March 15, 2017 10:31 PMCan't you do this? If you use TOP 100 PERCENT, you can use ORDER BY.
CREATE VIEW vwFirstInstances
AS
SELECT * FROM (
SELECT TOP 100 PERCENT
PatientID,
SymptomName,
Severity,
ConsentDate,
ROW_NUMBER() OVER (PARTITION BY PatientID, SymptomName ORDER BY ConsentDate) AS rn
FROM dbo.Diagnoses
ORDER BY PatientID, SymptomName, ConsentDate) x;
You can, however the order by will be ignored.
Order By to define order of rows returned has to go onto the outer query (the one that selects from the view)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2017 at 12:48 pm
Why not create a stored procedure or inline table function?
March 16, 2017 at 2:26 pm
Joe Torre - Thursday, March 16, 2017 12:48 PMWhy not create a stored procedure or inline table function?
Why? What's the advantage? Why do you think that you can exchange different object types?
March 16, 2017 at 2:36 pm
Luis Cazares - Thursday, March 16, 2017 2:26 PMJoe Torre - Thursday, March 16, 2017 12:48 PMWhy not create a stored procedure or inline table function?Why? What's the advantage? Why do you think that you can exchange different object types?
In a stored procedure or table function you can sort. A stored procedure compiles with the execution plan saving that overhead. Functions and stored procedures support parameters. I don't think you can "exchange object types". As for if an sp or function can be used in a given case, it depends where or how one plans to use the object.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply