Month: February 2010

Training

TekPub

Almost two years ago, in a rant titled Y Kant Developers Read?, I lamented the fact that I’m finding that less and less of my peers are reading books to gain knowledge. In the last two years, that has gotten even worse. Several programmer’s magazines/journal publishers have gone bankrupt and the book publishing industry is in the crapper. Programmers are either getting information from blogs, user groups, or conferences. Unfortunately, I fear that for the majority of programmers none of that is true. Interviewing programmers, I’ve found that most of them truly can’t code FizzBuzz or a singleton or any other fairly simple problem that I put in front of them.

Entering into the programmer learning void recently have been screencasts. I’ve long been a fan of DimeCasts (Link Removed) as a way to get introduced to a lot of different topics. I learned about (read: finally understood) Dependency Injection / Inversion of Control from DimeCasts and I think they are good people. Additionally, one way that a lot of people learned about ASP.Net MVC was from Rob Conery’s screencasts building the MVC storefront on ASP.Net (apparently – according to Rob – they’ve since been moved).

TekPub Logo
Enter TekPub. Rob Conery and James Avery started a company to produce high quality screencasts to teach programmers about topics like NHibernate with Ayende, Git, jQuery, ASP.Net MVC, Ruby on Rails, Linux, LINQ, and more. I talked my boss into buying me a year’s subscription and I am EXTREMELY pleased with how he spent the money 😉 They offer streaming video as well as downloads in standard and iPhone format. I’ve watched probably at least 15 screencasts so far and I have learned a LOT. I keep several videos on my phone and whenever I have spare time, I watch a few minutes.

Steve Sanderson’s MVC 2 screencasts are exceptionally money. I wish I had the words to explain the kinds of lights of understanding that went off in my head after watching these videos. They alone are worth the price of admission. But, they aren’t alone… I got a lot out of Justin Etheredge’s LINQ series as well as Rob’s “Build Your Own Blog” stuff. If your employer has any kind of training budget, you should get them to drop the $200.00 yearly subscription fee for you. Neither of you will regret it. In fact, even if they don’t, you should truly consider investing the $200.00 in your own future.

Code Tips

Row_Number() Part Two

Last Time, we looked at ROW_NUMBER() and saw a little of how it works and how we could use it to remove duplicates. Another one of my favorite uses for ROW_NUMBER() is to get only one row from a one-to-many join. I’ve had situations at work where I need to return one row per Person, but each person had several addresses, several injury codes, and several contacts (people working their case).

In many of those cases, there is nothing to indicate which one of those rows should receive precidence over the others. One might just select from those tables and then take just the MIN() of the primary key, grouping by all other fields, but that makes for very messy and hard to maintain code in my opinion. ROW_NUMBER() gives us a much easier way to handle this problem.

Examine this sql script that gives us a Person Table and a PersonAddress table. One person can have many addresses, but there is no way in the schema to give precidence to one of the addresses over any of the others. 

USE tempdb
GO

CREATE TABLE Person (PersonId INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(50))
GO

INSERT Person ([Name])
SELECT 'Bill'
UNION
SELECT 'Bob'
UNION
SELECT 'Carl'
UNION
SELECT 'Sue'
UNION
SELECT 'Cleo'
UNION
SELECT 'Nick'
UNION
SELECT 'Thomas'
UNION
SELECT 'Jeydro'
UNION
SELECT 'Ronald'
UNION
SELECT 'Jamie'
UNION
SELECT 'Colleen'
UNION
SELECT 'Denise'
UNION
SELECT 'Phil'
UNION
SELECT 'Jim'
UNION
SELECT 'Jack'
UNION
SELECT 'Kathleen'
GO

CREATE TABLE PersonAddress (PersonAddressId INT IDENTITY(1,1) PRIMARY KEY, PersonId INT, [Address] VARCHAR(100))
GO

INSERT PersonAddress (PersonId, [Address]) 
SELECT PersonId, CONVERT(VARCHAR, PersonId * 123) + ' ' + UPPER(REVERSE([Name])) + ' ST.'
FROM Person
ORDER BY PersonId DESC
GO

INSERT PersonAddress (PersonId, [Address]) 
SELECT PersonId, CONVERT(VARCHAR, PersonId * 321) + ' SQL DR.'
FROM Person
WHERE PersonId BETWEEN 3 AND 9
GO

SELECT p.PersonId, p.[Name], pa.[Address]
FROM Person p
INNER JOIN PersonAddress pa
ON p.PersonId = pa.PersonId
ORDER BY p.PersonId
GO

DROP TABLE Person 
GO
DROP TABLE PersonAddress
GO

Here is a sample of the results:
One to Many Addresses

Using a similar trick to what we used last time to find unique records, we can alter our select statement so that we only return one address per person. If we change our select statement to this

; WITH Addresses
AS 
(
	SELECT p.PersonId, p.[Name], pa.[Address], ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY p.PersonId ASC) AS RowNum  
	FROM Person p
	INNER JOIN PersonAddress pa
	ON p.PersonId = pa.PersonId
)
SELECT PersonId, [Name], [Address], RowNum
FROM Addresses
GO

We get the following results:
One to Many Addresses With Row Numbers

You can see now that all we have to do is limit the result where the RowNum is equal to one and we have something that we can use (the CTE) in further joins or in reports to ensure that we don’t introduce duplicates to our query.

; WITH Addresses
AS 
(
	SELECT p.PersonId, p.[Name], pa.[Address], ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY p.PersonId ASC) AS RowNum  
	FROM Person p
	INNER JOIN PersonAddress pa
	ON p.PersonId = pa.PersonId
)
SELECT PersonId, [Name], [Address]
FROM Addresses
WHERE RowNum = 1
GO

Our results:
One to One Addresses Thanks to RowNumber