I know this. (Ask me if I care.) Anyway, all of those folks who dislike me usually say "is there a way to shut him up." Well there is a way!!!! Have me train for a marathon. Ran 18 miles on Saturday and then a 9.3 mile race on Sunday inside the “gates“ in Central Park. I was very quiet all weekend. (Marathon in 12 days)

Another thing people thought was impossible was referencing a RANK()ing function in a WHERE clause in SQL Server 2005 or using an aggregate with the Ranking and Windowing functions. A simple solution that I did not see in any of the docs, use a Common Table Expression. Take this simple aggregate, rounding to the nearest 100th the sales figure from AdventureWorks;

Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt

From Sales.SalesOrderHeader

Group by CustomerID

Gives you results like:

CustomerID  totalamt

----------- -----------

22814       0

11407       0

28387       600

15675       7900

18546       0

(and so on)

What if you want to rank them? Easy, make the aggregate a CTE and rank over the new field:

--rank by totaldue, summed and rounded (nearest 100)

--need a CTE to do a sum & rounding

--so this example will have a

--customerID summed with all of

--their orders

With CustomerSum

As

(

Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt

From Sales.SalesOrderHeader

Group by CustomerID

)

Select *,

            Rank() Over (Order By totalamt Desc) as Rank

From CustomerSum

Results:

CustomerID  totalamt    Rank

----------- ----------- --------------------

678         1179800     1

697         1179400     2

170         1134700     3

328         1084400     4

514         1074100     5

(and so on)

Ditto if you want to filter a query by the results of a ranking function. Just move the ranking function up to the CTE:

--use a common table expression if you want

--to filter by one of the rows that contain a

--ranking function since ranking functions

--are not allowed in where or having clauses

With NumberRows

As

(

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RowNumber

From Sales.SalesOrderHeader

)

Select * from NumberRows

where RowNumber between 100 and 200

Order By SalesOrderID

Resutls:

SalesOrderID CustomerID  RowNumber

------------ ----------- --------------------

43758        27646       100

43759        13257       101

43760        16352       102

43761        16493       103

43762        27578       104

(and so on)