Re: Can I do this using SQL?

From: Dieter Noeth <dnoeth_at_gmx.de>
Date: Fri, 10 Feb 2006 13:09:04 +0100
Message-ID: <dshvq9$lco$1@online.de>

Roy Hann wrote:

>>SELECT *
>>FROM
>>  (
>>   SELECT
>>     branch_name,
>>     SUM(acct_balance) AS activity_tot,
>>     COUNT(cust_id) AS cust_tally,
>>     ROW_NUMBER() OVER (ORDER BY SUM(acct_balance) DESC) AS rn
>>   FROM BranchActivity
>>   GROUP BY branch_name
>>) dt
>>WHERE rn <= 10;

>
>
> Appealing but wrong. And I have the bruises to prove it.
>
> You are just arbitrarily choosing 10 rows, not the all rows with values that
> would place them in the top 10. (Consider the possibility that acct_balance
> is identical for all branches.)

That's why i wrote: "In Standard SQL you'll use ROW_NUMBER/RANK"

ROW_NUMBER -> exactly 10 rows
RANK -> then rows plus all the rows with the same value as the 10th. DENSE_RANK -> all the rows with the 10 highest distinct values

Dieter Received on Fri Feb 10 2006 - 06:09:04 CST