Re: Can I do this using SQL?

From: Dieter Noeth <dnoeth_at_gmx.de>
Date: Fri, 10 Feb 2006 10:12:03 +0100
Message-ID: <dshleb$ml8$1@online.de>

Tony Rogerson wrote:

> Is this one of the 'many' situations where properitary features come in to
> play while the standards committe catch up with stuff industry really needs.

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...
>
> 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).

In Standard SQL you'll use ROW_NUMBER/RANK. Teradata/Oracle/DB2 (and even SQL Server 2005) support it:

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;

Dieter Received on Fri Feb 10 2006 - 03:12:03 CST