Solved

TSQL View Pulling Data in Order

Posted on 2014-04-14
8
293 Views
Last Modified: 2014-04-14
Hello:

I have a TSQL view that contains three sections containing data on employees.  I need to modify the programming of my view to show each employee "in succession".

Each of the three sections contains Employees A, B, and C.

I need for the first row of the view for each section to reflect Employee A, the second row to reflect Employee B, and the final row to reflect Employee C.

So, when SQL pulls the data, I don't want to see the employees' data displayed in the following order:

Employee A
Employee B
Employee C
Employee A
Employee B
Employee C
Employee A
Employee B
Employee C.

Instead, I want to see the following:

Employee A
Employee A
Employee A
Employee B
Employee B
Employee B
Employee C
Employee C
Employee C.

What convention of TSQL syntax makes this possible for views?

Thanks!

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39999095
Order By EmployeeID (or whatever uniquely identifies an employee. Or Group By EmployeeID in some cases.  It depends on what you want to do.
Examples:
Select ID,FirstName,LastName,Address
           From Employees
           Order By ID

Open in new window


or

Select ID,FirstName,LastName,Address
           From Employees
           Group By ID

Open in new window

0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39999102
Sorting of data in SQL is handled by an ORDER BY clause.

Data in SQL Server is never guaranteed to be in any specific order (except along the clustered index).

So either specify ORDER by in your query that's querying the view or in the view definition (although something is niggling the back of my mind saying that ORDER BY in a view definition is not the best of ideas, but I can't quite place why).
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39999115
I thought that SQL won't let you "save" ORDER BY in a view.

TBSupport
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39999120
And ORDER BY clause is not allowed in Views. You need to mention ORDER BY while selecting from your view, say SELECT * FROM YourView ORDER BY EmpId
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39999127
You are correct.

I hadn't tested it when I replied above :)

Having just done so:

Msg 1033, Level 15, State 1, Procedure v1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.


So - end result is that you need to add ORDER BY to your queries to sort your data how you want it.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39999128
>I thought that SQL won't let you "save" ORDER BY in a view.
It does if you add TOP 100 PERCENT

SELECT TOP 100 PERCENT one, two, three
FROM your_table

I am absolutely convinced that the reason this is so is for Microsoft to be able to come up with completely ridiculous exam questions.
0
 
LVL 34

Expert Comment

by:ste5an
ID: 39999245
@Steve:
except along the clustered index
While it seems so, it's not guaranteed either. E.g. having multiple NUMA nodes or having multiple temp devices may influence the result gathering when paralellism is used. Or consider the use of an index in the query plan which has a different sort order.

@Jim: ORDER BY is not allowed in SQL-92 for a view. See also Order by. The basic reason is that a view should behave like a table. And a table is a simple unordered set of rows.
0
 
LVL 25

Expert Comment

by:SStory
ID: 39999934
I guess I assumed that was obvious.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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