How to return additional fields based on aggregate function ?

Question

I have two tables,

jobcli

• client_id
• job_id

jobs

• job_id
• jobTitle
• startDate
• projectmgr

What I am having trouble with is to display client_id, job_id, jobTitle, startDate, and projectmgr in a query, where the results are grouped by client_id and have max of date.

So far I can get a list that's groupped by client_id with their corresponding max(startDate).

SELECT client_id, MAX(startDate) AS LastJob FROM jobcli INNER JOIN jobs ON jobcli.job_id = dbo.jobs.id GROUP BY jobcli.client_id

This query only returns 2 fields, client_id and LastJob, but I also want to display job_id, projectmrg and title for the last job.

This scenario is for a SQL server 2005. Thanks for your help.

Answer 1

This is the greatest-n-per-group problem that has come up dozens of times on Stack Overflow.

Here's a couple of solutions:

SELECT j1.*
FROM jobcli j1
WHERE NOT EXISTS (
    SELECT * FROM jobcli j2 
    WHERE j1.client_id = j2.client_id 
      AND j1.startDate < j2.startDate
);

Or since you're using Microsoft SQL Server 2005, you can use windowing functions:

WITH rownumbered_jobcli AS (
  SELECT j.*, ROW_NUMBER() OVER (PARTITION BY j.client_id 
                                 ORDER BY j.startDate DESC) AS RN
  FROM jobcli;
)
SELECT * FROM rownumbered_jobcli WHERE RN = 1;