[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server view doesn't follow ORDER BY Clause

Posted on 2014-09-02
11
Medium Priority
?
155 Views
Last Modified: 2014-09-02
Hi

I have a table with 2 columns in SQL Server 2005:
- a currency number from a host system, which is a 3-digit number stored as text and is the PK of the table
- the 3-chararcter ISO code for the currency

I need to view this table "the other way around" with:
- the ISO code as the first column, sorted in ascending sequence
- the corresponding currency number as the second column

So I defined a corresponding view, for which the SQL looks perfect.

But when I open the view, it is still sorted by currency number (second column, on which I have NO sorting defined) although the SQL says ORDER BY ISO (first column) !!!

Is this a bug or a feature ?

Bernard
0
Comment
Question by:bthouin
11 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40298118
You don't put the ORDER BY in the view. You leave the view unordered and when you select from it then you can order the output.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40298135
Just do select * from yourview order by 1
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 40298151
hi,

Ideal scenario, your view should work.
Please share your view text and result.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 1

Author Comment

by:bthouin
ID: 40298165
I can't. This query (and more to follow) are used in an Excel spreadsheet where I bind them over the Data / Connections menus, the result being refreshed on Excel startup and stored in a sheet which is use as data validation source. Using the menus and defining the connection, there is NO WAY for me to indicate a sort /order by clause, so I'm stuck with the view as it is defined in SQL Server, which is plain wrong.

Anyway, why would the view offer a sort capability if it's not to be used ? And since when is a SQL statement with an ORDER BY clause not executed properly by SQL Server ? If I use such a clause from any application (Access, .Net, whatever) calling SQL Server, the latter always follows the ORDER BY. So why doesn't it follow the ORDER BY on its own view-based SQL statements ?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 40298166
as from SQL 2008, ORDER BY will not even be allowed inside a view (unless you also specify the TOP X or TOP X PERCENT for the SELECT)

so indeed, you cannot "define" the order by in a view anymore, this is a changed behavior.

note: I usually use stored procedures for querying data, there you can specify the order by without issues
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40298168
can you post a portion of your data, how your table is defined, and how your view is defined.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40298171
Precision: my post was an answer to Lee's post (first post after my question).

I will post my view and result.
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 1600 total points
ID: 40298178
After you create your connection, go to properties, definition, change command type from table to SQL and use select * from [whatever] order by 1
0
 
LVL 1

Author Comment

by:bthouin
ID: 40298211
Here the data and def's.
SQL-Server-view.docx
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40298221
Did you try modifying the connection per my last response?
0
 
LVL 1

Author Closing Comment

by:bthouin
ID: 40298257
Hi Randy

Yes, I just did, and after some fumbling with the syntax it worked perfectly !

Thanks a lot guys for your help. Now I even know how to tweak data connection definitions in Excel, and I will NOT use any sorting in my SQL Server views...:-)

Bernard
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question