# Monday, February 14, 2005

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)

 

 

 

 

posted on Monday, February 14, 2005 5:49:05 PM (Eastern Standard Time, UTC-05:00)  #    Comments [4] Trackback
# Friday, February 11, 2005
wow.
posted on Friday, February 11, 2005 2:32:54 PM (Eastern Standard Time, UTC-05:00)  #    Comments [30] Trackback
# Monday, February 7, 2005

I made the rounds Friday night on .NET Rocks. Chatted about Corzen, Yukon, RAID, the .NET Auction and of course my crazy adventures. You can listen here.

posted on Monday, February 7, 2005 12:22:34 PM (Eastern Standard Time, UTC-05:00)  #    Comments [11] Trackback
# Friday, February 4, 2005
Tonight I go live on .NET ROCKS (http://www.dotnetrocks.com/), my second interview and their 99th show! We are scheduled to talk about: SQL Server 2000 Interop SQL Server 2005 XQuery and other cool features .NET Charity Auction on eBay My upcoming marathon in Antarctica
posted on Friday, February 4, 2005 10:12:11 AM (Eastern Standard Time, UTC-05:00)  #    Comments [10] Trackback
# Wednesday, February 2, 2005
The winners for the The .NET Celebrity Auction for Aceh Aid at IDEP are set. The offical ebay price was $152.5, but several bidders bid higher, so we stand to earn $6000 for the charity!
posted on Wednesday, February 2, 2005 9:07:05 AM (Eastern Standard Time, UTC-05:00)  #    Comments [12] Trackback
# Thursday, January 27, 2005

There are now more bidders than .NET celebrities on the eBay auction to help victims of the tsunami in Indonesia. If you want an hour with your favorite .NET celebrity, you’re going to have to increase your bid.

http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=5552696499

posted on Thursday, January 27, 2005 8:07:30 AM (Eastern Standard Time, UTC-05:00)  #    Comments [21] Trackback
# Wednesday, January 26, 2005
posted on Wednesday, January 26, 2005 8:59:58 AM (Eastern Standard Time, UTC-05:00)  #    Comments [22] Trackback
# Monday, January 24, 2005

Lots of programmers have emailed me since the auction went live asking  “how can I help?” At this point, we have the auction up and running, the hard work of organizing is done. (Thanks to everyone, especially Julie and Richard for helping out this weekend!) So I say BID. They say, no no, I want to donate an hour. I say we have enough so BID BID! They say “What if I win?” I say “Cheap Bastard.”

So prove you are not cheap! Prove you like open source because of technical reasons, not cheapness reasons. Prove your ratty old clothes is a fashon statement. Bid now (don't be cheap): http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=5552696499 

posted on Monday, January 24, 2005 12:49:56 PM (Eastern Standard Time, UTC-05:00)  #    Comments [12] Trackback