Sorting Views

Hiya,

I'm back with another question.

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

Code:

---

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 :(

Create index on the view.

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

Excellent! The TOP 100 PERCENT suggestion is perfect!

Thanks for the help,
G

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

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?

Do you have Order by in the view?

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

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)

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

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

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.

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.