Solved

TSQL View Pulling Data in Order

Posted on 2014-04-14
8
287 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
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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 32

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now