SQLTeam.com | Weblogs | Forums

Get top record in a join


#1

I have 2 tables
table 1 "Employee":
id
employeeid
name

Table 2 "comments":
id
dateEntered
employeeid
comments

For each employee I need to get the last comment made by each employee. How can I do this?


#2

Something like this ought to do it.

select employeeid, max(name), max(comments), max(dateentered)
from employee e
join comments c
on e.employeeid = c.employeeid
group by employeeid

assuming employeeids are unique and there's a 1-1 mapping between employeeids and names and also between employeeids and dateentered. (because then the max() aggregators satisfy the grammar but don't actually do anything)


#3

I tend to do that using ROW_NUMBER

SELECT	employeeid,
	name, 
	comments, 
	dateentered
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY e.employeeid  
				ORDER BY e.employeeid, c.dateentered DESC
			),
		e.employeeid,
		e.name, 
		c.comments, 
		c.dateentered
	FROM	employee AS e
		join comments AS c
			on e.employeeid = c.employeeid
) AS X
WHERE	T_RowNumber = 1
ORDER BY name

as it lets me access any column in the Child Table's row


#4
SELECT e.name, c.employeeid, c.dateEntered, c.comments
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY employeeid ORDER BY id DESC) AS row_num
    FROM comments
) AS c
INNER JOIN employee e ON e.employeeid = c.employeeid
WHERE c.row_num = 1