BIT_AND

Declaration

BIT_AND(expr) OVER (analytic_clause)

Description

Returns the bitwise AND of all bits in expr.

The result type depends on whether the function parameters are evaluated as binary strings or numbers. If the parameter value is of the binary string type and is not a hexadecimal literal, bit literal, or NULL literal, it is evaluated as a binary string. Otherwise, the parameter value is converted to an unsigned 64-bit integer for numerical computation.

If no rows match, BIT_AND() returns a neutral value with the same length as the parameter value (all bits set to 1). NULL values do not affect the result unless all values are NULL, in which case the result is a neutral value with the same length as the parameter value.

Examples

CREATE TABLE product_information(supplier_id INT, product_id INT,list_price numeric, min_price numeric);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '45', NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1770', NULL, '70');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2370', '305', '247');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2380', '750', '731');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '3255', NULL, NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '35', NULL);

SELECT * FROM product_information;
+-------------+------------+------------+-----------+
| supplier_id | product_id | list_price | min_price |
+-------------+------------+------------+-----------+
|      102050 |       1659 |         45 |      NULL |
|      102050 |       1770 |       NULL |        70 |
|      102050 |       2370 |        305 |       247 |
|      102050 |       2380 |        750 |       731 |
|      102050 |       3255 |       NULL |      NULL |
|      102050 |       2380 |        750 |       731 |
|      102050 |       1659 |         35 |      NULL |
+-------------+------------+------------+-----------+
7 rows in set (0.003 sec)

SELECT product_id, BIT_AND(min_price) OVER (partition by product_id) BITS FROM product_information;
+------------+----------------------+
| product_id | BITS                 |
+------------+----------------------+
|       1659 | 18446744073709551615 |
|       1659 | 18446744073709551615 |
|       1770 |                   70 |
|       2370 |                  247 |
|       2380 |                  731 |
|       3255 | 18446744073709551615 |
+------------+----------------------+
6 rows in set (0.003 sec)