Results 1 to 13 of 13

Thread: Sorting Views

  1. #1
    Join Date
    Jul 2005
    Location
    Liverpool, UK
    Posts
    5

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,921
    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

  4. #4
    Join Date
    Jul 2005
    Location
    Liverpool, UK
    Posts
    5

    Smile

    Excellent! The TOP 100 PERCENT suggestion is perfect!

    Thanks for the help,
    G

  5. #5
    Join Date
    Nov 2008
    Posts
    1

    Great work

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

  6. #6
    Join Date
    Jan 2011
    Posts
    5

    Red face Sorting views

    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?

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,921

  8. #8
    Join Date
    Jan 2011
    Posts
    5
    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

  9. #9
    Join Date
    Jan 2011
    Posts
    5
    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)

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,921
    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

  11. #11
    Join Date
    Jan 2011
    Posts
    5
    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
    Attached Images Attached Images

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  13. #13
    Join Date
    Jan 2011
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •