Re: Can I do this using SQL?
Date: Fri, 10 Feb 2006 10:12:03 +0100
Message-ID: <dshleb$ml8$1_at_online.de>
Tony Rogerson wrote:
> Is this one of the 'many' situations where properitary features come in to
Standard already catched up, whereas vendors didn't. There's almost no
need for proprietary extensions if you check SQL1999/SQL2003.
> In SQL Server you would write...
In Standard SQL you'll use ROW_NUMBER/RANK.
Teradata/Oracle/DB2 (and even SQL Server 2005) support it:
SELECT *
> play while the standards committe catch up with stuff industry really needs.
>
> SELECT TOP 10 branch_name,
> SUM(acct_balance) AS activity_tot,
> COUNT(cust_id) AS cust_tally
> FROM BranchActivity
> GROUP BY branch_name
> ORDER BY activity_tot;
>
> Which gives you a result set you can use, whereas with standard SQL you
> would have to embed the SQL in the application (and not use stored
> procedures).
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;
Dieter Received on Fri Feb 10 2006 - 10:12:03 CET