SQL about select records that has max count

Question

SQL about select records that has max count

sqlfriend

SSC Guru

Points: 52781
More actions
January 19, 2012 at 11:56 pm

#266912

I have a query that wants to make a default orgUnitID to a Orgization.
Basically I check to see which Orgnization has the most count more OrgUnitId, then I will make that orgunitID as default orgunit ID for the Org.
I made a cte table first:
WITH cte AS (
SELECT
LEFT(pcc.CostCenter,2) AS org
,COUNT(orgUnitID)AS countOrgUnitID
,orgUnitID
FROm [dbo].[SAP_BasePositionCostCenter] pcc
JOIN .[dbo].[SAP_posPosition] pp
ON pcc.PositionNumber=pp.PositionNumber
GROUP BY LEFT(CostCenter,2), orgUnitID
)
Next I would like to only select Org, OrgUnitID, that has Max(countOrgUnitID),
but I cannot make it right,
any help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stephen7272 SSC Enthusiast Points: 165 More actions · Answer 1

sqlfriends (1/19/2012)
I have a query that wants to make a default orgUnitID to a Orgization.
Basically I check to see which Orgnization has the most count more OrgUnitId, then I will make that orgunitID as default orgunit ID for the Org.
I made a cte table first:
WITH cte AS (
SELECT
LEFT(pcc.CostCenter,2) AS org
,COUNT(orgUnitID)AS countOrgUnitID
,orgUnitID
FROm [dbo].[SAP_BasePositionCostCenter] pcc
JOIN .[dbo].[SAP_posPosition] pp
ON pcc.PositionNumber=pp.PositionNumber
GROUP BY LEFT(CostCenter,2), orgUnitID
)
Next I would like to only select Org, OrgUnitID, that has Max(countOrgUnitID),
but I cannot make it right,
any help would be appreciated.

select top 1 Org, OrgUnitID, countOrgUnitID from cte order by countOrgUnitID desc

But you may have more than one record with the maximum value:)

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

This is not working,

I want to return Org, orgUnitID that has the max count of orgUnitID

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

think you have been around here long enough to know that some table scripts / data and expected results will assist all of us 🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day