Every time you think you're getting ahead...

So, yesterday I was talking to one of my coworkers about my SQL class in grad school. I was telling him that it was challenging without be difficult, but that some of the problems on our SQL homework were more complicated than I would have expected. As proof, I sited an one of the early example problems we had in the class. We have a database that lists data from a series of foot races held annually for the past decade or so. Dr. Pardue wanted us to report the year of each race, the full name of the winner, the winner's age, and the average age of everyone racing that year. I've never been a SQL guru, but I'm familiar enough with the language that I can muddle through queries better than most of the folks in my class. Even though it took me almost an hour to get working right (mostly because I wasn't paying attention to my data set, and didn't take advantage of the columns available to me), I was pretty proud of myself for getting it done like I did. Here is the query I wrote.

SELECT
YEAR(tr1.RaceTime) As [Year],
trc1.FirstName + ' ' + trc1.LastName As Racer,
trr1.Time,
trr1.Age,
(
SELECT AVG (CAST (trr2.Age AS Float))
FROM tblRaceResult trr2
WHERE (YEAR(trr2.FinishTime) = YEAR(tr1.RaceTime))
) AS AverageAgeAll
FROM
tblRace tr1
INNER JOIN tblRaceResult trr1 on trr1.RaceID = tr1.RaceID
INNER JOIN tblRacer trc1 on trc1.RacerID = trr1.RacerID
WHERE
trr1.OverAllRank =
( SELECT MIN (trr2.OverAllRank)
FROM tblRaceResult trr2
WHERE (YEAR(trr2.FinishTime) = YEAR(tr1.RaceTime))
)
ORDER BY tr1.RaceTime


Then I showed the problem to my coworker (who is *much* better at this than I am). He took a quick look at the database and in 5 minutes, had a query that output the exact same answer I had, but in less than half the code it took me. Here's his query:

select
YEAR(rac.racetime) as 'Year'
,ISNULL(rcr.FirstName + ' ', '') + ISNULL(rcr.LastName, '') as Racer
,rr.Time
,rr.Age as Age
,(select AVG(CAST(age AS FLOAT)) from tblRaceResult where raceid = rac.raceid )AS 'Average Age'
FROM
tblRace rac
INNER JOIN
tblRaceResult rr ON rac.RaceID = rr.RaceID AND rr.OverAllRank = 1
INNER JOIN tblRacer rcr ON rr.RacerID = rcr.RacerID
ORDER BY 1

I was almost embarrassed that I didn't think of some of the things he did. Here's a few things I learned from reading my coworker's query:

1. I overthought the problem, when I used the year of the race to link the race table with the raceresults table. They have the RaceID as an FKey relationship, I should have just used that and saved myself the trouble.
2. I didn't know you could append conditions to Inner joins. Notice that James didn't need to use a where clause at all, he just included the "AND OverallRank = '1' " to his join statement.
3. I tend to make things harder than they should be. I need to look for the direct links between tables instead of assuming things are harder than they are, and try to outthink my way past a problem.

Anyway, it just goes to show you that no matter how good you think you're getting, there will always be someone better. I'm just really happy that I'm working with people again who I can learn from.

Rich

Public Speaking, Oh My!

I've been asked by the local LANUG (Lower Alabama .NET User Group) to speak at the next meeting, in September. I've talked it over with Ryan, the organizer, and we decided that the best topic I could present on would be the Social Networking for Developers address that Scott Hanselman gave at Devscovery. I hope he won't be upset. In fact, I need to email him and clear it with him, maybe ask for some help or pointers.

Lets see... what are the topics to cover...
1. Blogging for Devs (Why you should do it and Who you should be reading)
2. Social Networking (Facebook, LinkedIn, Twitter)
3. Community Code Sites (Stack Overflow, Sourceforge)
4. Team Utilities (gist.github.com, pastie.org, etherpad.com)

I should probably go over appropriate vs. inappropriate social activities while on the job and what you should or shouldn't put on a public blog, if I have time. I have to fill an hour's worth of content. I have 3 weeks to prepare for the lecture.

I'm looking forward to it, now I just have to find the time to get my presentation all squared away before then, without falling behind in school or work. Goody!

Rich

Dev Journal: The First Post!

So, this is my developer journal blog. I'm writing this after being inspired to dive deeper into the social networking sphere at the Devscovery conference in Redmond Washington, a couple weeks ago. Scott Hanselman gave the keynote about Social Networks for Developers. He made the case that Developers have come to rely on Social Networking for most of our developer questions. Think about it... when you need to look up the syntax or figure out how to solve a novel problem, what do you do? Do you walk over to your stash of tech books and thumb through the pages, trying to find what you want? No! You Google it (with Bing, if you're Scott)! When you Google your problem, 9 times out of 10, you'll find a post on a forum, a blog post, an article on a developer network, or some other related form of content that was put out on the web by another developer who had to solve the same problem you're now looking in to.

Scott made the case to all of us that keeping a developer blog is valuable to the community, and valuable to yourself. I don't know about any of you, but I have lost count of the number of times I have solved a problem and moved on with my work, only to run into the same or similar problem 6 months later, and don't have a clue how I solved it last time. So, I'm going to be using this blog discuss and archive these developer problems and solutions. When I find something that I think is worth saving, or solve a difficult problem, I will archive the info here.

I'm not a public figure, I'm not a ground breaking developer, I'm just a .NET junkie trying to make my way as a Software Engineer. This journal is more for my benefit than yours, but if you happen to stumble upon this blog and find something that saves your bacon on whatever project you're working on, be my guest. I'm not looking for comments or feedback, but if you have something to contribute to a thread, again... be my guest.

Anyway, that's all I have to say. If you find something that helps you, awesome. If not, back to Google with you!

Richard Akers