I have the following query:

SELECT first_name, age
FROM taba 
WHERE NOT EXISTS
(
    SELECT p.first_name, MAX(p.age) as age FROM taba p
    GROUP BY p.first_name
);

The inner sub-query finds the largest age for a given name. I want to basically find every row that isn't in the inner subquery result. What's the best way to achieve that? This query gives me the empty set and I'm not sure why.

Max Age By First Name

All Data

I want everything in all data that isn't in the max age by first name query.

CodePudding user response：

Using a correlated sub-query...

SELECT
  *
FROM
  taba  T
WHERE
  age < (
    SELECT MAX(age)
      FROM taba P
     WHERE T.first_name = P.first_name
  )

Using a sub-query and a join...

SELECT
  t.*
FROM
  taba  t
INNER JOIN
(
  SELECT first_name, MAX(age) AS max_age
    FROM taba
GROUP BY first_name
)
  AS age
    ON  age.first_name = t.first_name
    AND age.max_age    > t.age

Using EXECPT...

SELECT first_name, age FROM taba
EXCEPT
SELECT first_name, MAX(age) FROM taba GROUP BY first_name

Using EXISTS() (with a correlated sub-query)...

SELECT
  *
FROM
  taba   T
WHERE
  EXISTS (
    SELECT *
      FROM taba  P
     WHERE P.first_name = T.first_name
       AND P.age        > T.age
  )

CodePudding user response：

You need to refer to your outer table in your inner query to get this query worked.

SELECT first_name, age
FROM taba T
WHERE NOT EXISTS(SELECT NULL
                   FROM taba P
                  WHERE T.first_name = P.first_name
                    AND T.age = (SELECT MAX(age)
                                   FROM taba P2
                                  WHERE P.first_name = P2.first_name)
                  GROUP BY p.first_name
                );

You may try below shorter version also -

SELECT first_name, age
  FROM (SELECT first_name, age, RANK() OVER(PARTITION BY first_name ORDER BY age DESC) RNK
          FROM taba)
 WHERE RNK <> 1;

CodePudding user response：

Fiddle

There is one other solution to at least be aware of.

This avoids the correlated subquery and does not require a join.

A common solution:

SELECT *
  FROM taba
 WHERE (first_name, age) NOT IN (
         SELECT first_name, MAX(age)
           FROM taba
          GROUP BY first_name
       )
;

CodePudding user response：

One version can be:

SELECT b.first_name, b.age
  FROM taba b
 WHERE b.age < (SELECT Max(p.age)
                  FROM taba
                 WHERE p.first_name = b.first_name)