# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Sorting Views

## iamguyster

Hiya,

I'm back with another question.

I have a view of a table which contains users. the SQL is:



```
CREATE VIEW dbo.vwTravellers
AS
SELECT     TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value
FROM         dbo.tblTravellers
```

I would like to be able to sort the view by LastName, Firstname, but it doesnt allow a SORT BY in the view. Does anyone know of a cheeky little trick I could use to get around this?

Cheers
Guy

I wish I'd listened in that SQl Server training course I attended  :Frown:

----------


## MAK

Create index on the view.

----------


## skhanal

CREATE VIEW dbo.vwTravellers
AS
SELECT  TOP 100 PERCENT   TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value
FROM         dbo.tblTravellers
ORDER BY LastName, Firstname

----------


## iamguyster

Excellent! The TOP 100 PERCENT suggestion is perfect!

Thanks for the help,
G

----------


## Kiranu4u

It is great idea, the top 100 percent is working fine, it solved my problem  :Smilie:

----------


## dms

I realize this is an old post hopefully someone will notice....My issue is also how to sort a view I'm creating.

I tried using top 100 percent in my select statement with order by.  I'm using SQL Management studio 2008 GUI to create the view.  The query sorts correctly in the GUI interface but after the view is created when I select all columns on the newly created view, it is not sorted.  

What am I missing?

----------


## skhanal

Do you have Order by in the view?

----------


## dms

Yes I do.  Here's my generisized script:

CREATE VIEW [dbo].[vwNewView]
AS
SELECT     TOP (100) PERCENT dbo.vwDetails.EmpName
, dbo.vwDetails.JobTitle AS EmpJobTitle
, dbo.vwDetails.DeptName AS EmpDept
, dbo.vwParentsOnly.Name AS EmpSuper
,dbo.tblJobs.JobTitle AS SuperJobTitle

FROM         dbo.vwDetails INNER JOIN
                      dbo.vwParentsOnly ON dbo.vwDetails.Parent = dbo.vwParentsOnly.ID INNER JOIN
                      dbo.tblJobs ON dbo.vwParentsOnly.JobID = dbo.tblJobs.JobID

WHERE     (dbo.vwDetails.EmpName IS NOT NULL)

ORDER BY dbo.vwDetails.EmpName

----------


## dms

To follow up on why I need this to sort.  The view I've created is a list of employee names, their dept and job title, their supervisor and supers title.  The information is going to be used in a drop down list on an infopath form - hence the need for the employee's name to appear alphabetically.  (Maybe I can make infopath sort it on the field...hmmm?)

(Why isn't it already alphabetical?  Because the view it's built from is order by emp id)

----------


## skhanal

Can you check the query plan for SELECT * FROM vwNewView to see if SORT is included?

Also change
SELECT TOP (100) PERCENT dbo.vwDetails.EmpName

to

SELECT TOP 100 PERCENT dbo.vwDetails.EmpName

----------


## dms

Thanks for the suggestions - I removed the () surrounding 100 and recreated the view - same result, EmpName does not sort.

Attached is screen capture of result with partial names erased to protect the innocent.


script used:
CREATE VIEW [dbo].[vwNewView2]

AS

SELECTTOP 100 PERCENT dbo.vwDetails.EmpName
,dbo.vwDetails.JobTitle AS EmpJobTitle
,dbo.vwDetails.DeptName AS EmpDept
,dbo.vwFTEParentsOnly.Name AS EmpSuper
,dbo.tblJobs.JobTitle AS SuperJobTitle

FROM dbo.vwFTEDetails

INNER JOIN dbo.vwParentsOnly ON dbo.vwDetails.Parent = dbo.vwParentsOnly.ID 

INNER JOIN dbo.tblJobs ON dbo.vwParentsOnly.JobID = dbo.tblJobs.JobID

WHERE     (dbo.vwDetails.EmpName IS NOT NULL)

ORDER BY dbo.vwDetails.EmpName

----------


## rmiao

Per MS, sorting is not supproted in view so should do it outside. Someone found workaround to specify huge number in top like 'top 1000000' for 1000 rows table, but may stop working anytime in new service pack or hotfix.

----------


## dms

Thanks.  My understanding _was_ that views cannot be sorted but after reading these postings - it sounded like there might be a work around.  Noting the date of the posts, I suspect you're correct...a hotfix or something obliterated it's usefulness since these posts were written.

----------

