Finding The Top One Value Per Group

I ran into an issue the other day where I had a table like this:

ID	name	location	comment	comment_time
1	John	home	hello	1/1/2011 01:16:23
2	John	work	comment2	1/12/2011 13:23:10
3	John	home	blah	1/13/2011 02:02:02
4	Bill	work	smurf	1/15/2011 23:59:00
5	Bill	home	jello	1/17/2011 13:30:00
6	Bill	home	snafu	1/18/2011 12:00:19
7	Frank	work	batman	1/19/2011 14:15:16

Somebody came up to me and said "Hey John, I would like to see the last comment made by each person in the database and where they were when they made the comment". The logical answer is to use a Group By query. The problem is that you can't include a non-aggregate field in a Group By query which means I can't get at both the location and the comment in single query. I need to get a set of data that I can query against to get the result set i need. So each row in the return set has to be unique. I would love to use this subquery:

SELECT TOP(1) id, name

FROM table1

GROUP BY name

ORDER BY comment_time desc

But you get the following error:

Msg 8120, Level 16, State 1, Line 63

Column 'dbo.table1.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So I have to use an aggregate function (SUM, AVG, MIN, MAX or COUNT). At first I thought about using the MAX function on the ID to give me the highest ID but that assumes that the comment_time field is generated on INSERT and isn't updateable (for example, it wouldn't work if the comment_times for ids 2 and 3 were switched). Since I don't know and can't control that I decided to use MAX on the comment_time field, like this:

SELECT MAX(comment_time) as comment_time, name

FROM table1

GROUP BY name

Which gives me:

comment_time	name
1/13/2011 02:02:02	John
1/19/2011 14:15:16	Frank
1/18/2011 12:00:19	Bill

You have to alias the MAX(comment_time) or else it will return the column with the header "(No Column Name)" which wold make it difficult to use it in the next step.

Now I can use that set to get the result set i want like this:

SELECT name, location, comment, date

FROM table1

JOIN

(SELECT MAX(comment_time) as comment_time, name

FROM table1

GROUP BY name) as sub

ON table1.name = sub.name

AND table1.comment_time = sub.comment_time

ORDER BY

table1.comment_time

Which gives us:

name	location	comment	comment_time
John	Home	blah	1/13/2011 02:02:02
Bill	home	snafu	1/18/2011 12:00:19
Frank	work	batman	1/19/2011 14:15:16

The only problem might be if you have two comments by the same person in the same day at exactly the same time. If the completion time is generated by the server on insert, you shouldn't have this problem but if it is generated by the application you might (especially if the field is editable). My answer is that if two comments were made at the same time, they are both the last comment and should both be returned so my solution works fine (for my needs). The other problem is that my solution doesn't work if you need to return more than one row per group.

Let me know in the comments if you have a different solution.

Hasta Luego,

RutherfordTX

Updating a Table Using a Select

I recently had a situation at work where someone had exported data from our SQL Database into excel so they could update a column. Then they asked me to figure out how to update the data "my way", as they called it, so they didn't have to manually update all the changes. Coming from more of a programming background my first thought is to do things procedurally. I have done quite a bit of Perl and PHP coding so I would usually reach for a nice For Each loop and do it that way. Since T-SQL is set based I am having to retrain my brain to think in sets rather than procedures.

So I had a table like this:

pk_num	new_val
1	NULL
2	NULL
3	NULL

And I was given back a table like this:

pk_num	new_val
1	Frank
2	Joe
3	Sam

After scratching my head and looking through my big stack of books I decided the best way to do this would be a subquery. Lets call the original table table1, I imported the new table into SQL and called it table2. Here is the code I ended up using:

UPDATE table1

SET

new_val = table2.new_val

FROM

(SELECT pk_num, new_val

FROM table2) AS table2

WHERE

table1.pk_num = table2.pk_num

---------
EDIT: 8/16/2011
Since I am not using a where clause in the subquery, it is not actually needed. The query could be rewritten like it is below. I'm leaving it as an example and because it is the basis for the next part
UPDATE table1

SET

new_val = table2.new_val

FROM table2

WHERE

table1.pk_num = table2.pk_num

---------

It was a bit more complicated than that in that I didn't want to overwrite existing values in the new_val field in the original table if there was no value in the field in the update table so the end result actually looked like this:

UPDATE table1

SET

new_val = table2.new_val

FROM

(SELECT pk_num, new_val

FROM table2

WHERE new_val IS NOT null) AS table2

WHERE

table1.pk_num = table2.pk_num

Hope this helps someone. I know this is pretty basic stuff, but when you are just starting with T-SQL or, like me, are use to working with procedural or object oriented programming languages its's hard to train your brain to think in concepts like subqueries.

Hasta Luego,

RutherfordTX