• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

TSQL View Pulling Data in Order

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
TBSupport
Asked:
TBSupport
1 Solution
 
SStoryCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
TBSupportAuthor Commented:
I thought that SQL won't let you "save" ORDER BY in a view.

TBSupport
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Harish VargheseProject LeaderCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
ste5anSenior DeveloperCommented:
@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
 
SStoryCommented:
I guess I assumed that was obvious.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now