You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment (Line numbers are included for reference only):
01 SELECT *
02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,
03 ………………..
04 FROM Customer
05 JOIN SalesOrder
06 ON Customer.CustomerID = SalesOrder.CustomerID
07 GROUP BY Customer.CustomerID) AS DonationsToFilter
08 WHERE FilterCriteria <= 100
You need to insert a Transact-SQL clause in line 03 to complete the query. Which Transact-SQL clause should you insert?
A.
RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
B.
NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
C.
ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
D.
DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
I have seen the answer to this question to be NTILE. And here it is DENSE_RANK. Which one is right?
0
0
http://msdn.microsoft.com/en-us/library/ms173825.aspx
DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
0
0
the key words are:
highest 100 different amounts
0
0