Post reply

Avoid order by to use a sql in a view

  • November 12, 2008 at 1:14 pm

    #195947

    Hi,

    i have a sql which works:

    select

    [Customer No_],

    sum(Amount) as Balance

    from [Detailed Cust_ Ledg_ Entry]

    group by

    [Customer No_]

    order by

    [Customer No_]

    when i want to use this sql in a view - i receive this error:

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

    Does anybody know a work around for this - would be great 😀

    Greetings form Austria!

  • November 12, 2008 at 1:23 pm

    #897756

    Give this a shot:

    CREATE VIEW viewname AS

    SELECT TOP 100 PERCENT

    [Customer No_],

    sum(Amount) as Balance

    from [Detailed Cust_ Ledg_ Entry]

    group by

    [Customer No_]

    order by

    [Customer No_]

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • November 12, 2008 at 1:38 pm

    #897761

    That is valid, but the order by will not be honoured. Order bys are only honoured by the query processor if they are in the outermost select statement, i.e. the one that selects from the view.

    Define the view without the order by and then use the order by in the query that selects from the view.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 12, 2008 at 1:49 pm

    #897768

    Here is more informaiton on this issue

    http://cf-bill.blogspot.com/2007/01/sql-server-order-view.html]

  • November 12, 2008 at 2:00 pm

    #897775

    Oh, thanks for reminding me... should have remembered that! I recall when we updated an application to SQL 2005, the users reported that their sorts were not honored in their reports (sort defined in the view)

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • November 13, 2008 at 12:04 am

    #897908

    GilaMonster: Thank you for your quick answer

    Ali: your link does not work - please update the working one.

    Thank you

  • November 13, 2008 at 12:27 am

    #897911

    Juergen Gleiss (11/13/2008)

    Ali: your link does not work - please update the working one.

    Try removing the square bracket from the end of the URL

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply