Search code examples
sqloracle-databasewhere-clausehaving-clause

Oracle Sql Query to find ids which has value of an attribute and at the same time not having diff values

enter image description here

Hi Team, I need a simple query where I can the partner id with contract status='C' which does not have any contract status in 'A' and 'B'. I need to find the Partner id of row number 3 since the partner id 2 does not have any contract status of A or B. Similarly, I want to filter partner ids which have contract status 'A' or 'B' but not contract status 'C' . That is , I need the partner id of row number 5,6. Thanks in advance

Solution

  • Yet another option is to use the analytical function MAX as follows:

    SELECT *
  FROM (SELECT T.*,
               MAX(STATUS) OVER(PARTITION BY PARTNERID) AS MAX_STATUS
          FROM T)
WHERE MAX_STATUS = 'A';