January 19, 2012 at 11:56 pm
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.
January 20, 2012 at 5:01 am
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:)
January 20, 2012 at 11:01 am
This is not working,
I want to return Org, orgUnitID that has the max count of orgUnitID
January 20, 2012 at 11:08 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply