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

mysql where-clause having-clause
Drewneedshelp picture Drewneedshelp · Mar 9, 2011 · Viewed 25.9k times · Source
  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

geekosaur picture geekosaur · Mar 9, 2011

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

Related questions

WHERE vs HAVING

Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL? And are there any downsides instead of doing WHERE 1 (writing the whole definition instead of a column name)?

Read More
SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
SQL WHERE condition is not equal to?

Is it possible to negate a where clause? e.g. DELETE * FROM table WHERE id != 2;

Read More