I have tried the CTE and subquery approaches in my query below but it didn't work.

I have two tables with two columns; I would like to use only one column for the join condition, union all, and group by as per the requirement. I would like to fetch the other column without mentioning it among the group by columns. How can achieve this?

SELECT BusnPartClientSuppContractId
    SELECT A.BusnPartClientSuppContractId
    FROM tblBusnPartClientSuppContractDivBranch AS A
    WHERE A.BusnPartClientSuppContractId IN (
            SELECT BusnPartClientSuppContractId
            FROM @BusnpartIdsOfOtherContracts
        AND A.BusnPartClientSuppContractId NOT IN (
            SELECT TOP 1 BusnPartClientSuppContractId
            FROM @SuppClientDivBranch2


    SELECT B.BusnPartClientSuppContractId
    FROM @SuppClientDivBranch2 AS B
    ) tmp
GROUP BY BusnPartClientSuppContractId
ORDER BY DivBranchId

I need BusnPartClientSuppContractId but I dont want to include it in the group by clause.

  • 1
    So, you want GROUP BY DivBranchId only? – ypercubeᵀᴹ Oct 21 '16 at 15:45
  • How is the database supposed to decide which of the possibly multiple different BusnPartClientSuppContractId's it should display or how should it display multiples? You could use a window function as ypercube sugguested or could simply use a Min or Max on BusnPartClientSuppContractId to get the smallest/largest id. – Evan Steinbrenner Oct 21 '16 at 16:49
  • @EvanSteinbrenner if you are referring to the external query, it would display all of them, because each partition/group will have exactly 1 (from the HAVING clause) or not displayed at all. If you are referring to the internal TOP 1, yeah I agree. Something is fishy there. – ypercubeᵀᴹ Oct 21 '16 at 17:49
  • @ypercubeᵀᴹ My comment was directed at the OP who wanted to group by one thing and include a second thing that wasn't in the group by in the results. Presumably if you don't want to group by that second thing it is because their are multiple distinct values possible for it which leads directly to my question. What do you want it do there? Display one contractid based on some not included criteria, display a row for each of them or something else? – Evan Steinbrenner Oct 21 '16 at 18:34
  • @EvanSteinbrenner I agree, in the general case of such queries, this is a valid point. But in this specific case/query, that they want to have in the result only groups with one member/row, it is not needed. If they change the COUNT(*)=1 to something else, like COUNT(*)<=3 or remove it, then it would be a problem indeed. – ypercubeᵀᴹ Oct 21 '16 at 21:06

You can use a window aggregate function in a subquery (derived table or CTE, that's not important):

SELECT BusnPartClientSuppContractId,
    SELECT BusnPartClientSuppContractId,
           cnt = COUNT(*) OVER (PARTITION BY DivBranchId)
    FROM (
        -- the tmp subquery as it is, no change
        ) tmp
    ) t
WHERE cnt = 1
ORDER BY DivBranchId ;

Unrelated to the question but the TOP 1 subquery is not deterministic because it doesn't have an ORDER BY. So the whole query is not deterministic and may yield different results per execution, even if the data don't change.

  • You haven't tagged the question with a version, so I assumed 2005+. – ypercubeᵀᴹ Oct 21 '16 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.