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?

Originative

You can't simply "replace the WHERE with HAVING". The HAVING clause should appear in a different place in the query - after the GROUP BY and ORDER BY. BUT - as titanoboa says, I am not sure HAVING is what you are looking for. So I vote for him! – Dementia

Answer 1

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

Polyadelphous

I got my hopes up, and then I got the "You have an error in your SQL syntax . . ." error message. Simply changing the "WHERE" to "HAVING" did not work. :( – Originative

Replacing WHERE with HAVING does not work because HAVING cannot occur before GROUP BY. – Programme

@ Drewneedshelp - You got the error because you put the HAVING before the GROUP BY and ORDER BY. It should be at the end of the query. – Dementia

@Dementia in my case, having clause must be placed between GROUP BY and ORDER BY to make it all work. – Straddle

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.

Programme

"you should debug the string you insert into NOT IN - maybe it accidentally contains too many ids." – Originative

I agree with your answer 100%, which is why I wrote the query that way. However, I am not getting the expected behavior. Instead, it [omits any position where the MAXid is in the NOT in.] Your suggestion "debug the string you insert into NOT IN - maybe it accidentally contains too many ids." doesn't apply here. The actual query (that I'm running for my unit test) is as seen above (with only a single id to omit). Note: The T1.position does have another row (with id=1) that I'm expecting to get in the result set. – Originative

Answer 3

Valid syntax for HAVING is like this

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

Lard

If you have a position that has ids 0,1 and 2, this query will omit it completely because HAVING MAX(T1.id) NOT IN ( 2 ) does not match. It will not return 1 (which is the max id if 2 is ignored). – Programme

My answer is just the syntax for HAVING. I am not interpreting the question, which is not quite clear. In your example, the OP may be asking, because the max(id) of 2 is already marked for deletion, not to add another max(id) of 1. – Lard

titanoboa understands what I'm trying to do clearly. I agree with his answer (which supports my original query), but I'm not getting the expected behavior. – Originative

If you are not getting the expected behaviour, your query is wrong. With IN(2), If the id values for a position are 0, 1, 2 and you want id 1 to be selected, use your query, which has correct syntax. If you don't want id 1 then use the query with HAVING. – Lard

I agree that my syntax is OK. I am getting the result "1. Get all of the max IDs 2. Then exclude the ones that are in the NOT in". Hopefully, you see how that is "correct". What I want is "1. Omit all of the ids within NOT in. 2. Get the max IDs for each position". The two concepts are not the same. I have successfully written the query for the former concept. My question is "How do I write the query for the latter concept?" – Originative

It is the other way around. Your query does the second scenario, while the query with HAVING does the first scenario. In your query, 'WHERE id NOT IN ( 2 )' excludes the id=2 before it calculates the MAXid. The HAVING query, calculates all the possible MAXid values, then excludes the row than contains MAXid=2. – Lard

OK, I have put the code here: calllendar.com/pub/querytest.txt To run it, simply update the database connection section. I'm really interested to learn how fredt can be correct and I get the result that conflicts with his position. My guess is that he's right (because he seems to have experience) and that there is something I'm overlooking. However, there ain't much code (or data) to get confused about. Hopefully, posting the script in this way will make it easy for you to test and understand. – Originative

As your real query is different from the query in this question, I suggest you ask a new question with the real query and link to your script. Tag the question with both MySQL and PHP. – Lard

Here's the version fredt is implying gets a different row result set: caLLLendar.com/pub/querytestfredt.txt . It has been stripped back down to have the exact query as the original post. Note: The shorter version still returns the row set that conflicts with fredt's position. – Originative

As I said before, because you have a PHP script and the real subject of the question is really different from this one, and this question is stale, you'd better post a new question like "Unexpected PHP MySQL query result" with links to both scripts and lots of people would look at it and help. I don't do PHP. – Lard

Answer 4

did u try with

SELECT MAX(t1.id) AS MAXid 
FROM transactions t1 
WHERE id  <> ANY (2) 
GROUP BY t1.position ORDER BY t1.position

Behrens

I got a syntax error on this one too. I tried adding single quotes around the number (just in case). Nothing worked for me. – Originative

if there is single number then no need to add ', many ids are there then use ' like '2','3' – Behrens

ANY expects a subquery, that's the reason for the syntax error. And if you want to use a subquery, you probably want to use <> ALL instead of <> ANY. <> ANY checks if there is at least one entry in the result set of the subquery that is different from id - which does not make sense for your usecase at all. – Programme

It should work with <> ALL if you use a subquery like WHERE id <> ALL (SELECT '2' FROM transactions), but I do not see any gain compared to NOT IN. – Programme