 Thursday, December 09, 2004
SQL Server 2005-DENSE_RANK() and NTILE(n)
DENSE_RANK works exactly like RANK() but will remove the skipping of numbers in the tie.
Select SalesOrderID, CustomerID,
DENSE_RANK() Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 2
47997 2 2
49054 2 2
50216 2 2
51728 2 2
57044 2 2
63198 2 2
69488 2 2
44124 3 3
. . . More
NTile(n) will evenly divide all the results into approximately even pieces and assign each piece the same number in the resultset. A perfect example is the percent of 100 (like for an examination in University) or a percentile of runners in a road race.
Select SalesOrderID, CustomerID,
NTILE(10000) Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 2
47997 2 2
49054 2 2
50216 2 2
51728 2 3
57044 2 3
63198 2 3
69488 2 3
44124 3 4
. . . More
One last example will bring these all together in one SQL Statement and show the difference between all four ranking functions.
--Ranking All
use adventureworks
Select SalesOrderID as OrderID, CustomerID,
Row_Number() Over (Order By CustomerID) as RowNum,
RANK() Over (Order By CustomerID) as Rank,
DENSE_RANK() Over (Order By CustomerID) as DRank,
NTILE(10000) Over (Order By CustomerID) as NTile
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
OrderID CustomerID RowNum Rank DRank NTile
----------- ----------- -------------------- -------------------- -------------------- --------------------
43860 1 1 1 1 1
44501 1 2 1 1 1
45283 1 3 1 1 1
46042 1 4 1 1 1
46976 2 5 5 2 2
47997 2 6 5 2 2
49054 2 7 5 2 2
50216 2 8 5 2 2
51728 2 9 5 2 3
57044 2 10 5 2 3
63198 2 11 5 2 3
69488 2 12 5 2 3
44124 3 13 13 3 4
44791 3 14 13 3 4
. . . More
Wednesday, December 08, 2004
National Do Not Call Registry ....for your cell phone
In a few weeks, cell phone numbers are being released to telemarketing
companies and you will start to receive sales calls on cell phones.
Call this number from your cell phone 888-382-1222. It is the national Do
Not Call list. It blocks your number for 5 years. Please pass on,
Here is online form:
Guest Blogger Today-Rich Shapero (WILD ANIMUS)
WILD ANIMUS is a novel set on the West Coast of North America, from Los Angeles through Portland and Seattle up to Fairbanks, Alaska, and the wilderness beyond. The text reads like a naturalist's impressions of climbing Mt. Rainier, the Cascades, Mt. McKinley, and ultimately Alaska's remote volcano, Mt. Wrangell. The vivid descriptions of the botany and weather found at these high altitudes are a breathtaking combination of fire and ice.
WILD ANIMUS was written, in part, during author Rich Shapero's 400-mile solo trek through treacherous mountain terrain. Set in the late 1960s and early '70s, it is an acid-tinged climb through some of the most forbidding territory on the planet, and ultimately asks, "Which is more precious, a person's life or his vision?" Today he is guest writing on my blog, I hope that you will add lots of comments:
Ransom Altman, the protagonist of my novel, WILD ANIMUS, is a mountain climber who's not satisfied merely to summit peaks. He's on a quest for a level of meaning and truth accessible only in the wildest corners of the globe, and ultimately, he ascends Alaska's Mt. Wrangell with a single-minded purpose: to reunite himself with what he imagines to be "the source of love," his god, whom he calls "Animus."
Ransom's quest encourages climbers to ponder, "What it is that drives me, often at great risk, toward the summit?" And, "As I climb, am I running away from something, or towards it?" What is it that drives you toward the summit?
I've made an excerpt from WILD ANIMUS available online. It's called "Confrontation on Mt. Wrangell" and presents a scene where the climbing party must decide whether to take a dangerous route to the summit or less risky route that means they won't be able to summit the mountain on this expedition. What would you do? Here's a link to the excerpt:
A Confrontation on Mount Wrangell
http://www.patronsaintpr.com/samples/animus/animusobd.htm
Rich Shapero
SQL Server 2005 (Yukon) Ranking Functions-RANK()
RANK() works a lot like ROW_NUMBER() except that it will not break ties, you will not get a unique value for ties.
Select SalesOrderID, CustomerID,
RANK() Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 5
47997 2 5
49054 2 5
50216 2 5
51728 2 5
57044 2 5
63198 2 5
69488 2 5
44124 3 13
. . . More
Dense_Rank tomorrow...
Tuesday, December 07, 2004
SQL Server 2005 (Yukon) Ranking Functions-ROW_NUMBER()
SQL Server 2005 adds the functionality of a Ranking expression that can be added to your resultset that is based on a ranking algorithm being applied to a column that you specify. This will come in handy in .NET applications for paging and sorting in a grid as well as many other scenarios.
The most basic new ranking function is ROW_NUMBER(). ROW_NUMBER() returns a column as an expression that contains the row’s number in the result set. This is only a number used in the context of the resultset, if the result changes, the ROW_NUMBER() will change. The ROW_NUMBER() expression takes an ORDER BY statement with the column you want to use for the row count with an OVER operator as shown here:
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By SalesOrderID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By SalesOrderID
Results are shown here:
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43659 676 1
43660 117 2
43661 442 3
43662 227 4
43663 510 5
43664 397 6
43665 146 7
43666 511 8
43667 646 9
...More
Alternatively if you have an ORDER BY clause in your result set different than your ORDER BY in your ROW_NUMBER() expression
--Row_Number using a unique value, different order by
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By SalesOrderID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID --Different ORDER BY than in Row_NUMBER
The result is shown here:
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 202
44501 1 843
45283 1 1625
46042 1 2384
46976 2 3318
47997 2 4339
49054 2 5396
...More
If you choose the ROW_NUMBER() function to run against a non-unique column, it will break the tie and still produce a running count so no rows will have the same number. For example, CUSTOMERID can repeat in this example and there will be several ties, SQL Server will just produce a monotonically increasing number, which means nothing other than the number in the result set as shown here:
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
The result are shown here:
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 2
45283 1 3
46042 1 4
46976 2 5
47997 2 6
49054 2 7
50216 2 8
51728 2 9
57044 2 10
63198 2 11
69488 2 12
44124 3 13
. . . More
Tomorrow RANK()...
Monday, December 06, 2004
Throw the Bums Out
Anyone who ever used illegal steroids should be banned from the game, or Pete Rose should be allowed in. Lacking any integrity at this point Bud Selig (and Kofi Annan) must resign. He presided over this sham and lets the players union walk all over him. We don’t have this problem in the NFL because we have a strong commissioner. In Baseball if you fail a drug test you have too many chances to hide it or try again. Baseball needs to get tough.
Sunday, December 05, 2004
Marathon Training in full swing!
We did a New York Road Runners Race in Central Park on Saturday-15k (9.3 miles). The pace we did in this race would put Linda and I on a 4+ hour marathon. Antarctica here we come...
|
Last Name
|
First Name |
Sex/
Age
|
Bib
|
City
|
State
|
Finish
Time
|
Pace/
Mile
|
|
GURBISZ |
KATHLEEN |
F27 |
4288 |
NEW YORK |
NY |
1:23:52 |
9:01 |
|
VAROLI |
LINDA |
F33 |
5528 |
NEW YORK |
NY |
1:25:30 |
9:11 |
|
FORTE |
STEPHEN |
M32 |
4142 |
NEW YORK |
NY |
1:25:34 |
9:12 |
Wednesday, December 01, 2004
Killing me Softly..
When you think of the Microsoft Regional Directors what usually comes to mind are really amazing speakers at conferences like DevDays, Tech*ED (last year we had two RDs place #1 at two Tech*Eds) and the North Africa Developers Conference. You think of great authors and things like .NET Rocks.
RDs are truly great. But even if you collect together 150 amazing colleagues from around the world and put them all in a room together, there would be nothing without the proper organization. Leadership is key. General George Washington was less of a man (and General) without Martha (just ask the solders who she brought socks to).
For the past three years I have had the pleasure or working with the best PM at Microsoft, Eileen Crain. As of today Eileen is no longer the PM of the Regional Director Program or as I like to refer to it, the “RD Mom”. She has went on to bigger and better things at Microsoft.
Eileen has worked behind the scenes to make sure that the RDs got speaking engagements, in front of large customers, or any other kind of exposure. She also would always offer to take us out to dinner when we were in town! (Or drive me home when I drank too much. ). Whether it was planning a new marketing initiative or RD party at Tech*ED, she did it very thoroughly.
The RD program has been around for over 10 years and in years past you only heard of RDs at DevDays. Eileen has worked real hard to make us knows-and it worked. In the last few years the visibility of the RDs has grown and it is all due to Eileen.
Also Eileen has been someone who I would turn to for business advice and even personal advice. She would even pick up the phone at 2am when I was complaining about “the girl” or when a group of RDs would call (in a drunken stupor) from Cairo, Casablanca or Kuala Lumpur.
Eileen I will miss you and I wish you the best.
PS-people usually have to think really hard to figure out my titles most of the time, but this title was one of those “you had to be there ones” in Dallas at Tech*ED in 2003, a bunch of RDs (led by me!) got on stage and did “Killing me Softly” Karaoke and dedicated it Eileen.
Monday, November 29, 2004
35 Down, 4 to go...
On Saturday I reached the summit of the trail-less peak Fir Mountain. I am now only a few peaks away (4 peaks, 2 hikes) away from reaching membership to the Catskill 3500 Club.
The Catskill 3500 is a club where if you hiked the 35 peaks in the Catskill Range that are above 3500' in elevation. How do you get to 39 you ask? You have to do 4 of the peaks twice, in the winter time.
I should get in early next year! I have been working on them since February 2002. These hikes have been very rewarding and the views have been great!
Dad is nagging me to take him hiking in the Catskills, Slide Mountain on snowshoes will be the first peak I do after I get the other 4. Hope to see some of you there...
You climber snobs may say 3500' that’s it? To you I say become a member of the 3500 club and get back to me. :) Some of these climbs were just as challanging as any day on Kilimanjaro (except the last two days).
Monday, November 22, 2004
Traffic Free Central Park-A Reality?
The Departments of Transportation and Parks & Recreation announced a series of steps the City will take to increase recreational use of Central Park.
The DOT is going to close for good some entrances and implement carpooling, so there will be less cars to try to kill us. They did lower the speed limit to 25 mph, that will be hard to respect on the bike!
I hope the dream of a car-free Central Park becomes a reality! Rumor has it the city wants to do this...
This is a great step.
Thursday, November 18, 2004
A Soldier Responds
One of the great benefits of being an RD who speaks at conferences around the world is making friends all over. Usually my IM boxes open at any given time include at least someone from two or three different continents. While we all agree on development strategies and methodologies, we don’t always agree on politics. Friends can have a spirited discussion on sensitive topics and disagree-but remain friends. Here is an exchange between Boston RD Patrick Hynds, one of the smartest RD on the planet, myself (not so smart) and our friend in Egypt Mahmoud A. Gomaa.
Mahmound sent us a chilling graphic of a dead or injured Iraqi civilian. Below is our exchange.
Mahmound writes:
From: Mahmoud A. Gomaa
Sent: Thursday, November 18, 2004 2:06 AM
To: Stephen Forte; Hynds, Patrick
Subject: Falluja
Importance: High
this photo attached, is for an Iraqi civilian, killed while he was carrying his daughter, running from the wild attack of American troops in their compain againest terrorism!!!!!!!!!!!.
wondering where it is
Patrick writes back very eloquently:
From: Hynds, Patrick
Sent: Thursday, November 18, 2004 9:37 AM
To: Mahmoud@ieee.org; Stephen Forte
Subject: RE: Falluja
Mahmoud,
No one likes the consequences of war, unless they are insane. I regret what is happening in Iraq and what is happening in places torn by war without US involvement.
I am of Irish decent and I view what is happening in Iraq as very similar to what happened for decades in Northern Ireland. The British came in under circum |