Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: Syntax error using qualify?

    I am learning sql and am using the northwind database with ms sql server 2008.
    I am wanting to find the categoryname and the top 5 largest orders for each category for the year 1997.

    Here is my code that has an error on the last line with qualify. Can someone please tell me what did i do wrong?

    SELECT c.CategoryName, od.UnitPrice*quantity as Val
    FROM [Order Details] od, Products p, Categories c, Orders o
    WHERE od.ProductID = p. ProductID
    AND p.CategoryID = c.CategoryID
    AND od.OrderID = o.OrderID
    AND YEAR(o.OrderDate) = 1997
    qualify 5 <= rank() over (partition by CategoryName order by od.UnitPrice*quantity desc)

    I am wondering is my ms sql server doesnt have qualify as a command?
    Andrew

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    SQL Server doesn't support the 'qualify' sub clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since this is really a Microsoft SQL question, I've moved the thread from the ANSI SQL forum into the Microsoft SQL Server forum.

    Note that QUALIFY is a vendor specific add-on to SQL, it is not part of the ISO or ANSI standard SQL language. There is a Microsoft Connect item here that requests the feature. The request was closed "won't fix" because there is no official standard and there is an easy work around for this problem.

    -PatP
    Last edited by Pat Phelan; 01-01-14 at 11:12.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2013
    Posts
    25

    Ansi SQL

    Is it possible to solve this with ansi sql?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by northstar999 View Post
    Is it possible to solve this with ansi sql?
    Code:
    select CategoryName, 
           Val
    from (
        SELECT c.CategoryName, 
               od.UnitPrice*quantity as Val,
               rank() over (partition by CategoryName order by od.UnitPrice*quantity desc) as rnk
        FROM "Order Details" od, Products p, Categories c, Orders o
        WHERE od.ProductID = p. ProductID
        AND p.CategoryID = c.CategoryID
        AND od.OrderID = o.OrderID
        AND YEAR(o.OrderDate) = 1997
    ) t
    where 5 <= rnk
    Using [...] is not ANSI SQL and as you specifically requested ANSI SQL I used standard quoted identifiers with double qutoes.

    Additionally, the usage of the YEAR() function is also non-standard. In ANSI SQL that would be extract(year from o.OrderDate) but I don't know if SQL Server supports that.

    You might also want to read this: http://sqlblog.com/blogs/aaron_bertr...yle-joins.aspx
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Mar 2013
    Posts
    25
    Thank you for correcting me. I didnt realize i was writing bad code. Again, thank you.
    Andrew

Posting Permissions

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