October 9, 2012 at 8:17 am
okay hear me out... first 🙂
I Wanted to create a view with Order By clause so that the view to be ORDERED when I 'll use SELECT * query to this view
then I get error, couldn't use it... Obvious!
then I used
CREATE VIEW latestProducts
AS
SELECT TOP(10) ID, CATID, PRODUCTNAME, DESCRIPTION, DATEADD
FROM Products
ORDER BY DATEADD
and worked just fine.
! but my worry is that, is there any drawback/or bad practicing of this kind of Query???
thankx for your time 🙂
October 9, 2012 at 8:23 am
Ahmed_07 (10/9/2012)
okay hear me out... first 🙂I Wanted to create a view with Order By clause so that the view to be ORDERED when I 'll use SELECT * query to this view
then I get error, couldn't use it... Obvious!
then I used
CREATE VIEW latestProducts
AS
SELECT TOP(10) ID, CATID, PRODUCTNAME, DESCRIPTION, DATEADD
FROM Products
ORDER BY DATEADD
and worked just fine.
! but my worry is that, is there any drawback/or bad practicing of this kind of Query???
thankx for your time 🙂
Using order by without top is not allowed in a view, as you discovered. However, using an order by in the view does NOT guarantee the order of rows returned when selecting from the view. If you want your data ordered a certain there is one, and only one way to do that, add an order by to your query.
select Columns
from YourView
ORDER BY SomeColumn
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 8:30 am
It's only valid when you have a row-limiting TOP. So in you want the view to return the 10 most recent rows, then the view gets a TOP 10 and an ORDER BY on the date.
If you want to view to return all rows ordered (TOP 100 PERCENT ... ORDER BY) , then don't waste your time, the order by in a view does not guarantee row order, just the rows that are selected for the TOP.
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
October 9, 2012 at 9:52 am
INDEED JUST MET THIS RESULT AND ADDED ORDER BY TO MY QUERY, BUT DID WORKED THO WITH OTHER LESS COMPLEX QUERIES
SO IT DEPENDS 🙂
thankx for the replies guys 🙂
--------------------------------
Sean Lange (10/9/2012)
Ahmed_07 (10/9/2012)
okay hear me out... first 🙂I Wanted to create a view with Order By clause so that the view to be ORDERED when I 'll use SELECT * query to this view
then I get error, couldn't use it... Obvious!
then I used
CREATE VIEW latestProducts
AS
SELECT TOP(10) ID, CATID, PRODUCTNAME, DESCRIPTION, DATEADD
FROM Products
ORDER BY DATEADD
and worked just fine.
! but my worry is that, is there any drawback/or bad practicing of this kind of Query???
thankx for your time 🙂
Using order by without top is not allowed in a view, as you discovered. However, using an order by in the view does NOT guarantee the order of rows returned when selecting from the view. If you want your data ordered a certain there is one, and only one way to do that, add an order by to your query.
select Columns
from YourView
ORDER BY SomeColumn
[/b][/b]
October 9, 2012 at 9:54 am
No it doesn't depend. It may appear to be ordered correctly right now but there is no guarantee it will always be in the correct order. If you want ordered data, use an order by. Without it sql will return the data in the order it can retrieve it quickest.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 9:56 am
okey then, well noted 🙂
thankx again 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply