1

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 ,DivBranchId FROM ( SELECT A.BusnPartClientSuppContractId ,A.DivBranchId FROM tblBusnPartClientSuppContractDivBranch AS A WHERE A.BusnPartClientSuppContractId IN ( SELECT BusnPartClientSuppContractId FROM @BusnpartIdsOfOtherContracts ) AND A.BusnPartClientSuppContractId NOT IN ( SELECT TOP 1 BusnPartClientSuppContractId FROM @SuppClientDivBranch2 ) UNION ALL SELECT B.BusnPartClientSuppContractId ,B.DivBranchId FROM @SuppClientDivBranch2 AS B ) tmp GROUP BY BusnPartClientSuppContractId ,DivBranchId HAVING COUNT(*) = 1 ORDER BY DivBranchId

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

Improve this question
5
  • 1
    So, you want GROUP BY DivBranchId only? Commented Oct 21, 2016 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. Commented Oct 21, 2016 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. Commented Oct 21, 2016 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? Commented Oct 21, 2016 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. Commented Oct 21, 2016 at 21:06

1 Answer 1

Reset to default
4

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

SELECT BusnPartClientSuppContractId, DivBranchId FROM ( SELECT BusnPartClientSuppContractId, DivBranchId, 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.

Improve this answer
1
  • You haven't tagged the question with a version, so I assumed 2005+. Commented Oct 21, 2016 at 21:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.