开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜