Solved

TSQL View Pulling Data in Order

Posted on 2014-04-14
8
296 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 66

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 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