>我目前有以下查询
SELECT organisation.organisationID, COUNT(organisation.organisationID)
FROM position, positionLocation, organisation
WHERE position.positionLocationID = positionLocation.positionLocationID AND
positionLocation.organisationID = organisation.organisationID AND
position.status = 'Open'
GROUP BY organisation.organisationID;
此查询输出
organisationID | countOrganisationID
1 3
3 2
5 3
我想显示具有最大计数组织 ID 的记录。理想情况下,如果可能的话,我只想输出组织 ID 及其相应的组织名称。
类似的东西
organisationID | organisatioName
1 name1
5 name2
任何帮助将不胜感激
谢谢
> Barrett是对的,RANK()是要走的路,例如:
SELECT organisationID, c FROM (
SELECT organisationID
,c
,RANK() OVER (ORDER BY c DESC) r
FROM (
SELECT organisation.organisationID
,COUNT(organisation.organisationID) AS c
FROM position, positionLocation, organisation
WHERE position.positionLocationID = positionLocation.positionLocationID
AND positionLocation.organisationID = organisation.organisationID
AND position.status = 'Open'
GROUP BY organisation.organisationID
)
) WHERE r = 1;
可以子查询它:
WITH counts AS (
SELECT organisation.organisationID
,organisation.organisationName
,COUNT(organisation.organisationID) the_count
FROM position, positionLocation, organisation
WHERE position.positionLocationID = positionLocation.positionLocationID
AND positionLocation.organisationID = organisation.organisationID
AND position.status = 'Open'
GROUP BY organisation.organisationID, organisation.organisationName
)
SELECT organisationID, organisationName
FROM counts
WHERE the_count = (SELECT MAX(the_count) FROM counts)
这应该有效。
SELECT organisationID, organisatioName
FROM position, positionLocation, organisation
WHERE position.positionLocationID = positionLocation.positionLocationID AND
positionLocation.organisationID = organisation.organisationID AND
position.status = 'Open'
AND COUNT(organisation.organisationID) =
SELECT MAX(cnt) AS MaxCnt FROM
SELECT organisation.organisationID, COUNT(organisation.organisationID) AS cnt
FROM organisation
WHERE position.status = 'Open'
GROUP BY organisation.organisationID
GROUP BY organisation.organisationID, organisation.organisatioName;