I'm unable to filter out all non-duplicate id rows from the below query. It returns a group by query (grouping to get max execution times).

The issue is im trying to view only records that include contain ALL values in the WHERE clause, not ANY values (pn.name and pv.value_literal)

I can see when i order by object.id that i can see duplicate object.id in the table, which means those are the id's that contain all values in the WHERE clause.

How can i filter this query down to ONLY display the records where there are duplicate object.id in the table?

SELECT c.object_id             AS "OBJECT_ID",
       c.object_name           AS "OBJECT_NAME",
       cs.object_status        AS "OBJECT_STATUS",
       pn.name                AS "PROPERTY_NAME",
       pv.value_literal       AS "PROPERTY_VALUE",
       ca.object_status        AS "ACTIVE",
       Max(cl.execution_time) AS "LAST_OBJECT_EXECUTION"
FROM   object_log_hist cl
       JOIN object_table c
        ON c.object_id = cl.object_id
       JOIN object_status ca
        ON ca.object_status_id = c.status
       JOIN object_property cp
         ON cl.object_id = cp.object_id
       JOIN object_property_name pn
         ON cp.property_id = pn.id
       JOIN object_property_valid_value pv
         ON pn.id = pv.name_id 
       JOIN object_status cs
         ON cs.object_status_id = cl.execution_status    
WHERE  (
        (pn.name = 'propertyName1' AND pv.value_literal = 'production') OR
        (pn.name = 'propertyName2' AND pv.value_literal = 'testing')
       )
AND    cs.object_status = 'Complete'
GROUP  BY c.object_id,
          c.object_name,
          cs.object_status,
          ca.object_status,
          pn.name,
          pv.value_literal
ORDER BY c.object_id;

Table is currently like this:

And i want to just see something like this:

CodePudding user response：

You can use the COUNT analytic function:

SELECT your_columns
FROM   (
  SELECT your_columns,
         COUNT(*) OVER (PARTITION BY object_id) AS num_duplicates
  FROM   your_query
)
WHERE  num_duplicates > 1;

Which for your query would be:

SELECT OBJECT_ID,
       OBJECT_NAME,
       OBJECT_STATUS,
       PROPERTY_NAME,
       PROPERTY_VALUE,
       ACTIVE,
       LAST_OBJECT_EXECUTION
FROM   (
  SELECT c.object_id             AS OBJECT_ID,
         c.object_name           AS OBJECT_NAME,
         cs.object_status        AS OBJECT_STATUS,
         pn.name                 AS PROPERTY_NAME,
         pv.value_literal        AS PROPERTY_VALUE,
         ca.object_status        AS ACTIVE,
         Max(cl.execution_time)  AS LAST_OBJECT_EXECUTION,
         COUNT(*) OVER (PARTITION BY object_id) AS num_duplicates
  FROM   object_log_hist cl
         JOIN object_table c
         ON c.object_id = cl.object_id
         JOIN object_status ca
         ON ca.object_status_id = c.status
         JOIN object_property cp
         ON cl.object_id = cp.object_id
         JOIN object_property_name pn
         ON cp.property_id = pn.id
         JOIN object_property_valid_value pv
         ON pn.id = pv.name_id 
         JOIN object_status cs
         ON cs.object_status_id = cl.execution_status    
  WHERE  (pn.name, pv.value_literal) IN (
           ('propertyName1', 'production'),
           ('propertyName2', 'testing')
         )
  AND    cs.object_status = 'Complete'
  GROUP  BY c.object_id,
            c.object_name,
            cs.object_status,
            ca.object_status,
            pn.name,
            pv.value_literal
  ORDER BY c.object_id
)
WHERE  num_duplicates > 1;