How to replace IN CLAUSE USING EXISTS?-CodePudding

select 
    TV.ATTRIBUTE
FROM
    TABLE_VALUE TV
WHERE 
    TV.NUMBERS IN (SELECT MAX(TV1.NUMBERS) FROM TABLE_VALUE TV1 
                    WHERE TV.UNIQUE_ID=TV1.UNIQUE_ID GROUP BY UNIQUE_ID )

CodePudding user response：

I'm not sure exists would help here, because - as you put it - for each unique_id there be many numbers values, and you want to select attribute for highest numbers for that particular unique_id.

exists is useful when you want to check whether something ... well, exists, but that's not the case here.

CodePudding user response：

You do not want EXISTS, instead you can use the RANK or DENSE_RANK analytic functions:

SELECT attribute
FROM   (
  SELECT attribute,
         DENSE_RANK() OVER (PARTITION BY unique_id ORDER BY numbers DESC) AS rnk
  FROM   table_value
)
WHERE  rnk = 1

or use the MAX analytic function:

SELECT attribute
FROM   (
  SELECT attribute,
         numbers,
         MAX(numbers) OVER (PARTITION BY unique_id) AS max_numbers
  FROM   table_value
)
WHERE  numbers = max_numbers;

Either option will only read from the table once.

If you really did want to use EXISTS (or IN) then it will be less efficient as you will query the same table twice but you can do it with a HAVING clause:

SELECT tv.attribute
FROM   table_value tv
WHERE  EXISTS(
  SELECT 1
  FROM   table_value tv1
  WHERE  tv1.unique_id = tv.unique_id
  HAVING MAX(tv1.numbers) = tv.numbers
)

fiddle