Post reply

NTILE vs DENSE_RANK

  • October 20, 2010 at 7:37 pm

    #230110

    View the highest 100 different amounts that were donated. So write a query to list the 100 amounts. 1 SELECT *

    2 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,

    3

    4 FROM Customer

    5 JOIN SalesORder

    6 ON Customer.CustomerID = SalesOrder.CustomerID

    7 GROUP BY Customer.CustomerID) AS DonationsToFilter

    8 WHERE FilterCriteria <= 100

    In order of complete the query, you have to insert a Transact-SQL clause in line 3.

    What would be answer to the above query from the below?

    A) DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC)

    B) NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC)

  • October 20, 2010 at 9:15 pm

    #1240037

    I assume that you've tried each of these? What did you find out when you tested them?

    BTW, what college do you go to? What is your professor's name?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 20, 2010 at 9:25 pm

    #1240039

    I dont have a SQL 2005/2008 to test. Could you please tell me where would I test it (online)?

  • October 20, 2010 at 10:13 pm

    #1240045

    SATISD9X (10/20/2010)

    I dont have a SQL 2005/2008 to test. Could you please tell me where would I test it (online)?

    No excuse... here's a free copy for you to install and test with.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

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