Archive for October 28, 2012

The ROW_NUMBER Function As An Alternate To The MAX Function

It has taken a month to fuss over my new blog, but I finally made my first SQL entry. Since I’m excited about the upcoming SQL Pass conference, I thought I would show a fictitious problem about employees and their interests in SQL Conferences.

Problem: You are given two tables. The first table contains employees. The second table contains all the SQL Conferences each employee has been interested in along with the date they showed interest in the conference and whether or not they are still interested. You are asked to find the last SQL Conference that was added for each employee. Only conferences the employees are still interested in should be included, and only one conference per employee should be listed. The returned data should be ordered by the employee’s last name and first name.

Employee and Interest Data Model

The first solution that came to mind, was to use the MAX function on the InterestAddDate field to find the last added interest. There are two issues with this approach though.

1. In order to get the activity field returned, the Interest table has to be joined a second time on the MAX(InerestAddDate).
2. Multiple rows will be returned if the employee had an interest in two SQL conferences on the same date. While this could be a valid result set, in this case only one activity should be returned.


WITH CTE_InterestsByMax
AS
(
      SELECT
         EmployeeID
         ,MAX(InterestAddDate) AS LastInterestAdDate
      FROM
            dbo.Interest AS i
      WHERE
            isActive = 1
      GROUP BY
            EmployeeID
)
SELECT
      e.FirstName + ‘ ‘ + e.LastName AS EmployeeName
      ,i.InterestAddDate
      ,i.Activity
FROM
      CTE_InterestsByMax AS im
      JOIN dbo.Interest AS i ON im.LastInterestAdDate = i.InterestAddDate
                                                AND im.EmployeeID = i.EmployeeID
      JOIN dbo.Employee AS e ON im.EmployeeID = e.EmployeeID
ORDER BY
      e.LastName
      ,e.FirstName

Solution: To address these two issues, I used a Common Table Express (CTE)  and the ROW_NUMBER function. This function will number each row with a unique sequential number based on the OVER clause. Inside the OVER clause, I will order the data by the InterestAddDate field in descending order. Since I want to find the last SQL Conference of interest for each employee, I’m going to add the PARTITION statement on the EmployeeID field to the OVER clause. This will cause the ROW_NUMBER function to start over for each EmployeeID. Since I’m not using an aggregate function, I can return all the data from the Interest table that I need.

In the next part of the query , I join the CTE to the Employee table and add a WHERE clause. Since I ordered each partition in descending order, I know that the first row of each partition will have a rowindex of 1. I can now filter my data by rowindex = 1.

WITH CTE_InterestsByRow_Number
AS
(
      SELECT
         i.EmployeeID
         ,i.InterestAddDate
         ,i.Activity
         ,ROW_NUMBER() OVER (PARTITION BY i.EmployeeID ORDER BY i.InterestAddDate DESC) AS RowIndex
      FROM
         dbo.Interest AS i
      WHERE
         i.IsActive = 1
 )
SELECT
      e.FirstName + ‘ ‘ + e.LastName AS EmployeeName
      ,i.InterestAddDate
      ,i.Activity
FROM
      CTE_InterestsByRow_Number AS i
      JOIN dbo.Employee AS e ON i.EmployeeID = e.EmployeeID
WHERE
      rowindex = 1
ORDER BY
      e.LastName
      ,e.FirstName

When I looked at the logical reads for these two separate queries, the query using the MAX function had twice as many logical reads as the query with the ROW_NUMBER function. When I looked at the Execution Plan for both queries, I found the query using the MAX function had a higher Query Cost relative to the batch. My first run with the data, I used 20 Employees and 40 Interests. For the second run, I used 1000 employees and 4000 interests. I found that the Query Cost for the query using the MAX function increased with the larger datasets.

Execution Plan

Pimping My SQL Saturday T-Shirt

I’m very excited about attending my first SQL Pass Summit in Seattle in a few weeks. I haven’t even attended yet and I’m so impressed with the schedule, extracurricular events, and the mentors they assign first-timers like myself.

One of the events they have schedule is on Wednesday. They want everyone to wear their SQL Saturday t-shirts. There is only one problem…..mine makes me look like a house. I absolutely refuse to where a men’s t-shirt that makes me look like that. So I tapped into the right side of my brain and pimped my t-shirt out. Now not only do I look like a girl, but a stylish one.

20121020-154000.jpg
Not looking too great.
20121020-154459.jpg 20121020-154014.jpg
Look out how happy I look. Now I look like a SQL girl.

Growing up my grandmothers taught me crocheting and sewing. (My maternal grandmother taught me in Spanish. ) Now a days, I just go to Google or You-Tube and search for the technique I want to learn. I found out how to alter a neckline and how to add a hoodie. Here are the steps I went through.

Step 1. I researched all the techniques I needed to learn in order to feel comfortable making the changes to my precious SQL Saturday t-shirt. I answered questions like…

  • What kind of needle should I use?
  • Do I need special thread?
  • Do I need any special notions like binding? (Notions are all the little extras you use for sewing, like grommets for the hoodie, clasps, binding,… The stores have whole walls for notions.)
  • How do you sew a hoodie?

Here are the sites I found most helpful based on the sewing skills I already posses. If you have never sewn, I would suggest looking for some YouTube videos on the subject as well.

Step 2. I figured out what aspects of my SQL Saturday t-shirt I wanted to alter

  • Neckline – I prefer a scoop neck or v neck. (Next time I will make a v neck.)
  • Shoulders – The shoulder seams needed to be moved up to my shoulders. I think this, out of all the issues with my t-shirt, made me look the boxiest. By moving the seams up to the edge of my shoulder where they belonged, I improved the look of my t-shirt immensely.
  • Sleeves – I’m only 5’6, so my sleeves needed to be shorter. Since I had an extra t-shirt that was in a contrasting color, I used it to change the color of my sleeves.
  • Length – I shortened the t-shirt so that it sat on my hips properly
  • Hoodie – Because I just needed it.


Step 3.
I marked up my t-shirt so that I knew where I needed to cut it. While the video tutorials talked about using scissors to cut the t-shirt up, I found using my rotary cutter much easier. I folded my t-shirt in half, making sure there were no wrinkles, then I cut the sides, arms, and neckline out. Since I folded the t-shirt in half, I knew my t-shirt would remain symmetric.

20121020-161202.jpg 20121020-161210.jpg
Clocks and plates make
great templates for cutting circles.

Step 4. I created a pattern for the hoodie based on my favorite hoodie in my closet. I did buy some red t-shirt material for the hoodie, but I could have used another t-shirt. Since Red Gate is my absolute favorite toolset, I cut their logo out of a second t-shirt I had and put it in my hood. I used that same t-shirt for my sleeves and the rest of the hoodie.

Step 5. I sewed the hoodie together.

Step 6. I sewed the sides and the sleeves.

Step 7. I attached the hoodie to the neckline. I did this by finding the center line of the back of the t-shirt, and the center line of the hoodie. I started sewing from there so that I knew my t-shirt would be symmetric. since the hood will be down more than up, I have the seam facing out.

Step 8. I used my twin needle and a contrasting color thread to sew the neckline. I used the same twin needle to sew the hem as well.

20121020-161235.jpg 20121020-161228.jpg
20121020-161243.jpg

Hello World!

When I taught beginning programming with Microsoft Visual Basic, I always used the traditional “Hello World!” to teach how to create a msgbox. Since this is my first Blog, I thought I would do the same.

You may ask, why am I starting a Blog, and why should you read it? Here are my goals:

  • Share with the T-SQL community what I have learned over the years.
  • Share with the BI community my knowledge with Microsoft SQL Reporting Services.
  • Better myself as a writer.

I hope you enjoy my posts.

%d bloggers like this: