我需要选择我所达到的最大聚合值的“作业角色desc”。这张表如下所示。
[Job Role Description] | [Number Of Placements]
------------------------------------------------
Training BI 24
System Analyst 23
Data Analyst 24
Data consultant 25
DB programmer 24job_role_desc列来自由job_role_id连接的另一个表。我使用了下面的代码来实现这一点。
SELECT
job_role_dim.job_role_desc AS "Job Role Description" ,
SUM(fact_accounts.no_of_placements) AS "Number Of Placements"
FROM
fact_accounts
INNER JOIN
job_role_dim ON job_role_dim.job_role_id = fact_accounts.fk3_job_role_id
GROUP BY
job_role_dim.job_role_desc如何修改上述代码,使其仅显示具有最大放置数的job_role_desc?谢谢你的帮助。
发布于 2016-02-06 03:20:54
您可以使用row_number()或dense_rank()
SELECT ja.*
FROM (SELECT j.job_role_desc AS "Job Role Description" ,
SUM(a.no_of_placements) AS "Number Of Placements",
ROW_NUMBER() OVER (ORDER BY SUM(a.no_of_placements) DESC) as seqnum
FROM fact_accounts a INNER JOIN
job_role_dim j
ON j.job_role_id = a.fk3_job_role_id
GROUP BY j.job_role_desc
) ja
WHERE seqnum = 1;如果希望使用最大的所有版本,则使用dense_rank()或rank()。
https://stackoverflow.com/questions/35236925
复制相似问题