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_at_online.de>
>
>
> 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.)
Date: Fri, 10 Feb 2006 13:09:04 +0100
Message-ID: <dshvq9$lco$1_at_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 - 13:09:04 CET