Is there a "NOT HAVING" syntax like "WHERE XXX NOT IN"?

Question

1. I have a few queries get the ID numbers of rows that will be deleted in the future.
2. The row numbers are put into a string and placed in the query below (where you see "2").

3. I want the results to ignore the rows (as though they have already been deleted).

   SELECT MAX(T1.id) AS MAXid
   FROM transactions AS T1 
   WHERE id NOT IN ( 2 ) 
   GROUP BY T1.position 
   ORDER BY T1.position
   

My guess is that I need to replace the "WHERE" line with "HAVING", but I cannot find "NOT HAVING" syntax.

The way this query is currently written, it will not return a row for T1.position if the max id for the position is listed in the WHERE clause.

How do I get this query to give me the max ID for the T1.position while overlooking the rows with IDs listed in the WHERE clause?

Answer 1

HAVING id NOT IN (2) should work; [NOT] IN isn't limited to WHERE clauses.

Answer 2

HAVING is not what you need - it is only useful if you want to filter by MAX. For example, if you do not want to get all MAXids but only those larger than 2, you can use HAVING MAXid > 2.

As far as I understand, you want to ignore some rows and calculate the MAXid of the remaining rows. For this purpose, your statement looks correct to me. Afaics a position is not listed in the result set if all its ids are mentioned in your NOT IN clause. This is reasonable since there is nothing left you could calculate a MAX of. If some of a position's ids are listed in NOT IN, while others are not, you should get the MAX of those not listed in NOT IN.

If your result set does not match these expactations, you should debug the string you insert into NOT IN - maybe it accidentally contains too many ids.