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 *,
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
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
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By SalesOrderID) as RowNumber
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
