O'Reilly logo

Smart Business Intelligence Solutions with Microsoft® SQL Server® 2008 by Kevin S. Goff Lynn Langit Davide Mauri, Sahil Malik, and John C. Welch

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Rank Function and Combinations

Not only can we use TopCount to retrieve the top n members based on a measure, we can also use the Rank function to assign a sequential ranking number. Rank allows you to assign the specific order number of each result. Just like with TopCount, you’ll want to use a named set to fully optimize a Rank statement.

For example, suppose we want to rank states by Internet Sales Amount, showing sales for those states with sales that are non-zero. We need to do two things. First, we create a named set that sorts the states by Internet Sales Amount, and then we create a calculated member that ranks each state by the ordered set. The full query, which we’ll break down later, is shown here. The query results are shown in Figure 10-22 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required