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
Solved

TSQL View Pulling Data in Order

Posted on 2014-04-14
8
291 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 33

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

808 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