Monthly Archives: April 2010

24 Hours of PASS – SQL Server 2008 R2

Posted on by
4

Another entire day of presentations from leading speakers and experts and all about SQL Server? Sign me up. PASS has put together another  amazing online event, featuring 24 back-to-back webcasts covering SQL Server and Business Intelligence, starting at 12:00 (GMT) on the 19th May 2010.

Registration is entirely free, so go take a look at what sessions would suit your needs and sign up. There are several tracks that are covered in this event: Business Intelligence (BI), Database Administration (DBA), Development (DEV), Professional Development (PD) – each of which have several sessions devoted to them. You can see the list of available sessions here, but some to note are:

  • (BI) Solving common business problems with Microsoft PowerPivot – Donald Farmer (Blog | @Donalddotfarmer)
  • (BI) Easier than ever Report Authoring in SSRS 2008 – Jessica M. Moss (Blog | @jessicammoss)
  • (BI) Reporting Services Enhancements in SQL Server 2008 – Greg Low (Blog | @Greg_Low)
  • (DBA) What’s really happening on Your Server? 15 Powerful SQL Server Data Management Objects – Adam Machanic (Blog | @AdamMachanic)
  • (DBA) Top 10 Mistakes on SQL Server – Kevin Kline (Blog | @kekline)
  • (DBA) BLITZ! 60 Minute Server Takeovers – Brent Ozar (Blog | @BrentO)
  • (DEV) High Performance Functions – Simon Sabin (Blog | @simon_sabin)
  • (PD) Manage your DBA Career, Don’t Let it Manage You – Brad McGehee (Blog | @bradmcgehee)

So what are you waiting for? Go tell your boss you’re taking a training day. In fact tell him/her that you are going to get 3 days of training all packed into a single day and all he/she has to pay for is your time away from your desk. Considering what you are going to take away from this event, that’s a pretty good deal. By any standard. So stock up on whatever [legal] stimulants you are partial to (coffee/bacon/etc) and register for one, several, or if your name is Thomas LaRock, every single session and then screen cast your efforts using UStream. Read the 24 HOP Team’s interview with Tom for more insight.

Oh, and if you are going to follow (or indeed tweet about) the event on Twitter, use the #24HOP hashtag.

Rediscovering RANK() – Selecting the TOP N Rows for each Group or Category

Posted on by
1

There may be times when you need to get the top 10 (or n) rows from each group of your dataset to satisfy a business requirement. There are a few ways this can be done, but none so elegant as when you use RANK().

RANK() effectively applies a ranking to each rows in your dataset, based on a specified grouping and order. This can then be used to select the Top n rows by using an appropriate where clause. Here’s an example:

   1: USE Adventureworks

   2: go

   3:

   4: WITH OrdersCTE

   5: AS

   6:

   7: (

   8: Select

   9:     salespersonid,

  10:     customerid,

  11:     SUM(subtotal) Total,

  12:     RANK() OVER(PARTITION BY salespersonid ORDER BY SUM(subtotal) desc) Ranking

  13: from Sales.SalesOrderHeader

  14: Where

  15:     salespersonid is not null

  16: Group by

  17:     customerid,

  18:     salespersonid

  19: )

  20:

  21: Select

  22:     salespersonid,

  23:     customerid,

  24:     Total,

  25:     Ranking

  26: From OrdersCTE

  27: Where

  28:     Ranking <= 10

In the above example, I needed to find the top 10 Customers for each Sales Person based on the sum of all order subtotals (using AdventureWorks 2005). Here are the results:

Ranking Results

You can read more about the RANK() function here, and for more ranking functions see DENSE_RANK(), NTILE() & ROWNUMBER().