CODEWITHSUNDEEP
sqloracle-databaseoracle11g
amitl
amitl

Reputation: 33

Filter maximum value from sql count query

I currently have the following query

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;

This query outputs

organisationID | countOrganisationID
1                3
3                2
5                3

I would like to display records that have max countOrganisationID. Ideally i would just like output the organisationID with its corresponding organisationName if possible.

Something along the lines of

organisationID | organisatioName
1                name1
5                name2

Any help would be appreciate

Thanks

Upvotes: 3

Views: 1443

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Barrett is right, RANK() is the way to go, e.g.:

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;

Upvotes: 5

Jon Wilson
Jon Wilson

Reputation: 776

This should work.

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;

Upvotes: 1

Glenn
Glenn

Reputation: 9150

Could just subquery it:

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)

Upvotes: 4

Related Questions