Re: Can I do this using SQL?

Cool - even better! Once people catch up and are on the current version of SQL Server I'll be sure to give them that answer, for now I'll try and remember to post both.

Thanks Dieter.

Tony.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Dieter Noeth" <dnoeth_at_gmx.de> wrote in message 
news:dshleb$ml8$1_at_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