Search code examples
sqldatabasedb2

SQL get one result of counted column

I have a problem in DB2 getting exactly one (random) result from a column, that is actually counted. table: name, objA, objS, category, status, all varchar

I count all "name" where objA and objS are similar like this:

SELECT
objA,
objS,
COUNT (name) AS COUNT_NAME
FROM myTable
WHERE
category = 'testuser'
AND status NOT IN ('removed', 'invalid', 'suspended') 
GROUP BY
objA,
objB

This works. Now I want to add a column to the result, which has exactly one value of the "name" in it and I do not care, which one. I was going for the first.

Unfortunately, the FIRST_VALUE (name) OVER ( PARTITION BY name ORDER BY name ) testValue Requires an additional GROUP BY for "name" which completely destroys the purpose of the query. I was trying the dumb approach to just collect all names with LISTAGG and then get one of these. This would have worked but I get 4000+ results at one time so the DB2 just cancels the request (I see the point). Approach via subquery on top level got me only to get the first of all names, not the counted ones.

How can I get ONE result of the counted names without breaking the query?

Thanks in advance

Solution

  • Aggregating (min, max) should help you get one name value.

    For example of table orders as shown below: Orders Table

    You can make use of the following queries: example queries Which will give you resultset as follows: result set