All geek questions in one place

Is there a syntax "DOESN'T", such as "WHERE XXX NOT IN"?

  • I have several requests that get line IDs that will be deleted in the future.
  • Line numbers are placed in a line and placed in the query below (where you see "2").

  • I want the results to ignore the rows (as if they were already deleted).

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

I assume that I need to replace the string "WHERE" with "HAVING", but I can not find the syntax "NOT HAVING".

As this request is currently written, it will not return a string for T1.position if the maximum id for the position is specified in the WHERE clause.

How to get this request to give me the maximum ID for T1.position ignoring the lines with the identifiers specified in the WHERE clause?

+4
source share
4 answers

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

+4
source

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

As far as I understand, you want to ignore some lines and calculate the MAXid of the remaining lines. For this, your statement looks right to me. Afaiki position is not indicated in the result set if all its identifiers are indicated in your NOT IN clause. This is reasonable, since there is nothing that you could calculate MAX. If some of the item identifiers are specified in NOT IN and others are not , you should get MAX of those that are not specified in NOT IN.

If your result set does not match these conclusions, you should debug the line you insert into NOT IN - perhaps it contains too many identifiers.

+3
source

The valid syntax for HAVING is similar to 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
+1
source

tried using

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

Source: https://habr.com/ru/post/1342970/

More articles:


All Articles