# Filter rows Shape / rows keep or drop rows by a condition Page: ## Status Runs as a SQL step. arcform carries it as a `sql:` step today. ## The DuckDB step keep rows by a condition It reads the `orders` table the proofs share. Create it first: ```sql CREATE TABLE orders AS SELECT * FROM (VALUES (1, 'Alice Ng', 'north', DATE '2026-01-03', 120.00, 'widget', 'a|b', '{"channel":"web","items":[1,2]}'), (2, 'alice ng', 'north', DATE '2026-01-05', 80.50, 'gadget', 'b', '{"channel":"shop","items":[3]}'), (3, 'Bob Okafor', 'south', DATE '2026-01-05', 300.00, 'widget', 'a|c', '{"channel":"web","items":[4,5,6]}'), (4, 'Carla Díaz', 'east', DATE '2026-01-09', NULL, 'gizmo', NULL, '{"channel":"web","items":[]}'), (5, 'Bob Okafor', 'south', DATE '2026-01-12', 45.25, 'gadget', 'c', '{"channel":"phone","items":[7]}'), (6, 'Dev Patel', 'west', DATE '2026-01-12', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (7, 'Carla Diaz', 'east', DATE '2026-01-20', 99.99, 'gizmo', 'b|c', '{"channel":"shop","items":[10]}'), (8, 'Dev Patel', 'west', DATE '2026-02-02', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (9, 'Eve Larsen', NULL, DATE '2026-02-03', 15.00, 'gadget', NULL, '{"channel":"web","items":[11]}'), (10, 'Alice Ng', 'north', DATE '2026-02-10', 220.00, 'gizmo', 'a|b|c', '{"channel":"phone","items":[12,13]}') ) t(order_id, customer, region, order_date, amount, product, tags, meta); ``` Then run the statement: ```sql CREATE TEMP TABLE result AS SELECT * FROM orders WHERE amount > 100 AND region <> 'west'; SELECT * FROM result; SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) = [1, 3, 10] FROM result)) THEN 'as expected' ELSE error('the filter did not keep orders 1, 3 and 10') END AS verdict; ``` It needs nothing installed beyond DuckDB itself. ## Variants - [Keep rows with chosen values](https://meridian.online/operations/filter-rows/keep-rows-with-chosen-values): Keep or exclude the rows whose value is one of a picked list of values. - [Keep rows that contain text](https://meridian.online/operations/filter-rows/keep-rows-containing-text): Keep or drop the rows whose cells contain a string or match a pattern, in one column or any. - [Drop rows with missing or error values](https://meridian.online/operations/filter-rows/drop-rows-with-missing-values): Drop, or keep only, the rows that hold nulls, empty cells, NaNs or error values. - [Keep groups after aggregating](https://meridian.online/operations/filter-rows/keep-groups-after-aggregating): Keep the groups whose aggregate values meet a condition. - [Filter on a window function](https://meridian.online/operations/filter-rows/filter-on-a-window-function): Keep the rows whose window-function value meets a condition, such as the top row per partition. - Keep rows that match a condition: Keep, or drop, the rows for which a condition on their values is true. - Reuse a named filter: Define a filter condition once, by name or macro, and apply it in several queries. - Find the positions of matching rows: Return the row numbers at which a condition is true rather than the rows themselves. - Keep the selected rows: Keep or remove the rows a person has selected, highlighted or flagged in an interactive view. - Split rows into separate outputs: Send each row to one of two or more outputs by the condition it meets, so no row is discarded. - Keep rows past a watermark: Pass only the rows newer than the last successful run. ## What other tools call it Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2). - Azure Data Factory: [`Conditional split`](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-conditional-split) (read 2026-09-27) - Azure Data Factory: [`Filter`](https://learn.microsoft.com/en-us/azure/data-factory/control-flow-filter-activity) (read 2026-09-27) - Azure Data Factory: [`Filter`](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-filter) (read 2026-09-27) - Azure Data Factory: [`Row Filtering`](https://learn.microsoft.com/en-us/azure/data-factory/wrangling-functions) (read 2026-09-27) - Alteryx Designer: [`Filter`](https://help.alteryx.com/current/en/designer/tools/preparation/filter-tool.html) (read 2026-09-27) - Alteryx Designer: [`Filter In-DB`](https://help.alteryx.com/current/en/designer/tools/in-database/filter-in-db-tool.html) (read 2026-09-27) - Bruin: [`Filters`](https://getbruin.com/docs/bruin/core-concepts/semantic-layer.html) (read 2026-09-27) - Bruin: [`Segments`](https://getbruin.com/docs/bruin/core-concepts/semantic-layer.html) (read 2026-09-27) - Apache DataFusion: [`Filter (WHERE)`](https://datafusion.apache.org/user-guide/features.html) (read 2026-09-27) - Apache DataFusion: [`Filter post-aggregate (HAVING)`](https://datafusion.apache.org/user-guide/features.html) (read 2026-09-27) - Apache DataFusion: [`QUALIFY clause`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27) - Apache DataFusion: [`WHERE`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27) - Apache DataFusion: [`filter`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27) - DuckDB: [`HAVING (clause)`](https://duckdb.org/docs/current/sql/query_syntax/having) (read 2026-09-27) - DuckDB: [`QUALIFY (clause)`](https://duckdb.org/docs/current/sql/query_syntax/qualify) (read 2026-09-27) - DuckDB: [`WHERE (clause)`](https://duckdb.org/docs/current/sql/query_syntax/where) (read 2026-09-27) - Duckle: [`Filter Rows`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`Incremental Load`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - EasyMorph: [`Filter`](https://help.easymorph.com/doku.php?id=transformations:selectionfilter) (read 2026-09-27) - EasyMorph: [`Filter by condition`](https://help.easymorph.com/doku.php?id=transformations:filter) (read 2026-09-27) - EasyMorph: [`Filter by search`](https://help.easymorph.com/doku.php?id=transformations:searchfilter) (read 2026-09-27) - EasyMorph: [`Filter by type`](https://help.easymorph.com/doku.php?id=transformations:filterbytype) (read 2026-09-27) - EasyMorph: [`Remove empty rows`](https://help.easymorph.com/doku.php?id=transformations:removeemptyrows) (read 2026-09-27) - AWS Glue: [`Conditional Router`](https://docs.aws.amazon.com/glue/latest/dg/transforms-conditional-router.html) (read 2026-09-27) - AWS Glue: [`Filter`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-transforms-filter.html) (read 2026-09-27) - AWS Glue: [`Filter`](https://docs.aws.amazon.com/glue/latest/dg/transforms-filter.html) (read 2026-09-27) - AWS Glue: [`Remove null rows`](https://docs.aws.amazon.com/glue/latest/dg/transforms-remove-null-rows.html) (read 2026-09-27) - AWS Glue: [`SplitRows`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-transforms-SplitRows.html) (read 2026-09-27) - Apache Hop: [`Filter Rows`](https://hop.apache.org/manual/latest/pipeline/transforms/filterrows.html) (read 2026-09-27) - Apache Hop: [`Java Filter`](https://hop.apache.org/manual/latest/pipeline/transforms/javafilter.html) (read 2026-09-27) - Apache Hop: [`Switch / Case`](https://hop.apache.org/manual/latest/pipeline/transforms/switchcase.html) (read 2026-09-27) - Ibis: [`DropNull`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/relations.py) (read 2026-09-27) - Ibis: [`Filter`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/relations.py) (read 2026-09-27) - KNIME Analytics Platform: [`DB Row Filter`](https://api.hub.knime.com/nodes/*kCnGJ8ULaPafiaK5?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Expression Row Filter`](https://api.hub.knime.com/nodes/*l52oAk5X9QC1I-Qm?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Filter Apply`](https://api.hub.knime.com/nodes/*WjxQ1iEwpLnEYBFw?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Filter Apply Row Splitter`](https://api.hub.knime.com/nodes/*O8C2QJB6_qyz60FW?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Filter Definition Merger`](https://api.hub.knime.com/nodes/*EJHqn-eO1blWcKLb?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`HiLite Row Splitter`](https://api.hub.knime.com/nodes/*GrXjiUum6TCgQuKT?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Java Snippet Row Filter`](https://api.hub.knime.com/nodes/*gCkxohNct6nYF2Yz?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Java Snippet Row Splitter`](https://api.hub.knime.com/nodes/*Tfdrbm3BPahDguUq?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Nominal Value Row Filter`](https://api.hub.knime.com/nodes/*DBlot_cJykJ7wkzA?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Nominal Value Row Splitter`](https://api.hub.knime.com/nodes/*jtGcmWPYJ7TRqooA?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Row Filter`](https://api.hub.knime.com/nodes/*r8RWEOkU1NAO1cWu?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Row Filter (Labs)`](https://api.hub.knime.com/nodes/*0uNfWZO9ACGSSgBc?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Row Splitter`](https://api.hub.knime.com/nodes/*SEIDwYl2W8YHZAtn?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Row Splitter (Labs)`](https://api.hub.knime.com/nodes/*6LtUFSd2ccfh8oL5?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Rule-based Row Filter`](https://api.hub.knime.com/nodes/*Q-yW-3C9DumtInuz?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Rule-based Row Filter (Dictionary)`](https://api.hub.knime.com/nodes/*hwUzd18K73MkWZTq?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Rule-based Row Splitter`](https://api.hub.knime.com/nodes/*G8O2L3fpRpwYK8F_?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Rule-based Row Splitter (Dictionary)`](https://api.hub.knime.com/nodes/*R2VVZ_ctKSLwUCUM?details=full) (read 2026-09-27) - Apache NiFi: [`Filter Functions`](https://nifi.apache.org/nifi-docs/record-path-guide.html) (read 2026-09-27) - Apache NiFi: [`RouteText`](https://nifi.apache.org/components/org.apache.nifi.processors.standard.RouteText/) (read 2026-09-27) - Apache NiFi: [`ScriptedFilterRecord`](https://nifi.apache.org/components/org.apache.nifi.processors.script.ScriptedFilterRecord/) (read 2026-09-27) - OpenRefine: [`Facet by flag`](https://openrefine.org/docs/manual/facets#facet-by-star-or-flag) (read 2026-09-27) - OpenRefine: [`Facet by star`](https://openrefine.org/docs/manual/facets#facet-by-star-or-flag) (read 2026-09-27) - OpenRefine: [`Keep only matching rows`](https://github.com/OpenRefine/OpenRefine/blob/master/main/webapp/modules/core/scripts/views/data-table/data-table-view.js) (read 2026-09-27) - OpenRefine: [`Remove matching rows`](https://github.com/OpenRefine/OpenRefine/blob/master/main/webapp/modules/core/scripts/views/data-table/data-table-view.js) (read 2026-09-27) - OpenRefine: [`Text filter`](https://openrefine.org/docs/manual/facets#text-filter) (read 2026-09-27) - Pentaho Data Integration: [`Filter Rows`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Pentaho Data Integration: [`Java Filter`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Pentaho Data Integration: [`Switch / Case`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Polars: [`DataFrame.drop_nans`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27) - Polars: [`DataFrame.drop_nulls`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27) - Polars: [`DataFrame.filter`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27) - Polars: [`DataFrame.remove`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27) - Polars: [`Expr manipulation/selection: filtering (group)`](https://docs.pola.rs/api/python/stable/reference/expressions/modify_select.html) (read 2026-09-27) - Polars: [`GroupBy.having`](https://docs.pola.rs/api/python/stable/reference/dataframe/group_by.html) (read 2026-09-27) - Polars: [`arg_where`](https://docs.pola.rs/api/python/stable/reference/expressions/functions.html) (read 2026-09-27) - Power Query M: [`Table.FindText`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27) - Power Query M: [`Table.RemoveRowsWithErrors`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27) - Power Query M: [`Table.SelectRows`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27) - Power Query M: [`Table.SelectRowsWithErrors`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27) - RapidMiner Studio: [`Filter Documents (by Content)`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/filtering/filter_documents_by_content.html) (read 2026-09-27) - RapidMiner Studio: [`Filter Examples`](https://docs.rapidminer.com/latest/studio/operators/blending/examples/filter/filter_examples.html) (read 2026-09-27) - RapidMiner Studio: [`Filter Examples (In Database)`](https://docs.rapidminer.com/latest/studio/operators/extensions/In-Database%20Processing/indb_blending/indb_filter.html) (read 2026-09-27) - RapidMiner Studio: [`Filter Examples with Missing Values`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/filter_missing_examples.html) (read 2026-09-27) - Apache SeaTunnel: [`FilterRowKind`](https://seatunnel.apache.org/docs/2.3.13/transforms/filter-rowkind) (read 2026-09-27) - SQLMesh: [`@AND`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27) - SQLMesh: [`@HAVING`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27) - SQLMesh: [`@OR`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27) - SQLMesh: [`@WHERE`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27) - Tableau Prep: [`Calculation`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Exclude`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Filter`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Keep Only`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Null Values`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Range of Dates`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Range of Values`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Relative Date`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Selected Values`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Tableau Prep: [`Wildcard Match`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27) - Talend: [`tCombinedSQLFilter`](https://help.qlik.com/talend/en-US/components/8.0/combinedsql/combinedsql-component) (read 2026-09-27) - Talend: [`tFilterRow`](https://help.qlik.com/talend/en-US/components/8.0/processing/processing-component) (read 2026-09-27) - Talend: [`tSQLTemplateFilterRows`](https://help.qlik.com/talend/en-US/components/8.0/sqltemplate/sqltemplate-component) (read 2026-09-27) - Trifacta: [`Delete Transform`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/language-appendices/transforms.html) (read 2026-09-27) - Trifacta: [`Filter Transform`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/language-appendices/transforms.html) (read 2026-09-27) - Trifacta: [`Filter contains`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter custom formula`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter ends with`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter exact`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter greater than`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter in`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter less than`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter mismatched`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter missing`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter not equals`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Filter starts with`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Keep Transform`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/language-appendices/transforms.html) (read 2026-09-27) - VisiData: [`delete-cursor`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/canvas.py#L847) (read 2026-09-23) - VisiData: [`delete-selected`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/canvas_text.py#L169) (read 2026-09-23) - VisiData: [`delete-visible`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/canvas.py#L853) (read 2026-09-23) - VisiData: [`dive-cursor`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/canvas.py#L846) (read 2026-09-23) - VisiData: [`dive-except`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/freqtbl.py#L214) (read 2026-09-23) - VisiData: [`dive-except-selected`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/freqtbl.py#L215) (read 2026-09-23) - VisiData: [`dive-selected`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/canvas_text.py#L171) (read 2026-09-23) - VisiData: [`dive-visible`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/canvas.py#L852) (read 2026-09-23) - VisiData: [`dup-search`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/experimental/live_search.py#L36) (read 2026-09-23) - VisiData: [`dup-search-cols`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/experimental/live_search.py#L37) (read 2026-09-23) - VisiData: [`dup-selected`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/sheets.py#L1299) (read 2026-09-23) - VisiData: [`dup-selected-deep`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/sheets.py#L1301) (read 2026-09-23) - VisiData: [`open-selected`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/apps/vdsql/_ibis.py#L787) (read 2026-09-23)