How to return additional fields based on aggregate function?
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.
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;
精彩评论