8
  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?

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
Drewneedshelp
  • 85
  • 1
  • 2
  • 6
  • 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! – Galz Mar 09 '11 at 13:20

4 Answers4

6

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

geekosaur
  • 59,309
  • 11
  • 123
  • 114
  • 2
    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. :( – Drewneedshelp Mar 09 '11 at 10:48
  • Replacing WHERE with HAVING does not work because HAVING cannot occur before GROUP BY. – titanoboa Mar 09 '11 at 13:22
  • @ 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. – Galz Mar 09 '11 at 13:23
  • @Galz in my case, `having` clause must be placed between `GROUP BY` and `ORDER BY` to make it all work. – j.c Apr 17 '18 at 15:43
3

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.

titanoboa
  • 2,548
  • 15
  • 12
  • "you should debug the string you insert into NOT IN - maybe it accidentally contains too many ids." – Drewneedshelp Mar 09 '11 at 19:27
  • 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. – Drewneedshelp Mar 09 '11 at 19:36
1

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
fredt
  • 24,044
  • 3
  • 40
  • 61
  • 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). – titanoboa Mar 09 '11 at 13:20
  • 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. – fredt Mar 09 '11 at 14:10
  • 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. – Drewneedshelp Mar 09 '11 at 19:26
  • 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. – fredt Mar 09 '11 at 19:40
  • 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?" – Drewneedshelp Mar 10 '11 at 02:31
  • 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. – fredt Mar 10 '11 at 10:38
  • OK, I have put the code here: http://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. – Drewneedshelp Mar 20 '11 at 09:58
  • 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. – fredt Mar 20 '11 at 12:35
  • Here's the version fredt is implying gets a different row result set: http://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. – Drewneedshelp Mar 20 '11 at 15:42
  • 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. – fredt Mar 20 '11 at 16:27
0

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
xkeshav
  • 53,360
  • 44
  • 177
  • 245
  • I got a syntax error on this one too. I tried adding single quotes around the number (just in case). Nothing worked for me. – Drewneedshelp Mar 09 '11 at 10:53
  • if there is single number then no need to add `'`, many ids are there then use `'` like `'2','3'` – xkeshav Mar 09 '11 at 11:00
  • 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. – titanoboa Mar 09 '11 at 13:34
  • 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. – titanoboa Mar 09 '11 at 13:44