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 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
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.
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
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)
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
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.