group by with where not working

SELECT A.ID, A.COLUMN_B, A.COLUMN_C FROM A
WHERE A.COLUMN_A IN
 (
  SELECT A.COLUMN_A
  FROM B
  INNER JOIN A ON B."COLUMN_A" = A."COLUMN_A"
  WHERE B."COLUMN_B" = 'something'

  UNION

  SELECT A."COLUMN_A"
  FROM A  
  WHERE A."COLUMN_D" IN (X,Y开发者_如何学C,Z) OR A."COLUMN_D" = 'something'
  )

Now I want add a group by (A.ID) , and order by (A.COLUMN_B) DESC, and then select first to it. But DB won't allow. Any suggestions ? I can use LINQ to solve it once inner Union part is returned. But do now want to go that way.

---

There's a couple of things here.
First off - in DB2, when using GROUP BY, you can only select those columns listed in the grouping statement - everything else must be part of an aggregation function. So, grouping by a.Id and ordering by a.Column_B won't work - you'll need to order by SUM(a.Column_B) or something applicable.
Second... your query could use a bit of work in the general sense - specifically, you're self-joining twice, which you don't need to do at all. Try this instead:

SELECT a.Id, SUM(a.Column_B) as total, SUM(a.Column_C)
FROM a
WHERE a.Column_D in (X, Y, Z, 'Something')
OR EXISTS (SELECT '1'
           FROM b
           WHERE b.Column_A = a.Column_A
           AND b.Column_B = 'Something')
GROUP BY a.Id
ORDER BY total DESC
FETCH FIRST 1 ROW ONLY

Swap out the SUM function for whatever is appropriate.

---

You can't use a column in the ORDER BY or SELECT that you haven't included in the GROUP BY, unless it's being aggregated (in a function like MAX() or COUNT() or SUM().

So, you could GROUP BY A.ID,A.COLUMN_B, and then ORDER BY COLUMN_B. Using a TOP 1 should work, too.

I just noticed that you're on DB2. I know that it will work this way on SQLServer. DB2 should be similar.

---

Worked the oterh way around. Just used Order By on A.ID and select row with max identity column.