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?
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?
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)
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
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