SQLite DISTINCT: Remove Duplicate Rows from Query Results (Tested)

Ninad Pathak

October 7, 2026

Repeated values in a table come back repeated in a query, like the same city once for every order placed from it. SELECT DISTINCT returns each value once. In this article I’ll walk you through how it works in SQLite, and what to use instead of PostgreSQL’s DISTINCT ON.

TLDR

SELECT DISTINCT column FROM table; returns each different value once.
With several columns, DISTINCT removes rows that are identical in all of them.

NULLs count as equal, so all NULLs come back as a single row.
count(DISTINCT column) counts the different non-NULL values.
SQLite has no DISTINCT ON. Use row_number() in a subquery instead.

What is DISTINCT in SQLite?

DISTINCT is a keyword after SELECT that removes duplicate rows from the result. The SQLite SELECT docs say “duplicate rows are removed from the set of result rows before it is returned”. The opposite is SELECT ALL, which is the default and returns every row.

SQLite DISTINCT syntax

SELECT DISTINCT column1 [, column2, ...]
FROM table_name
[WHERE condition]
[ORDER BY ...];

SELECT count(DISTINCT column) FROM table_name;

DISTINCT applies to the whole row you select, not only the first column.

How to remove duplicates with DISTINCT

To show you how this works, I will use an orders table with one row per order:

sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, city TEXT, product TEXT NOT NULL, ordered_on TEXT NOT NULL);
sqlite> INSERT INTO orders (customer, city, product, ordered_on) VALUES ('ada', 'London', 'Widget', '2026-10-01'), ('ada', 'London', 'Gadget', '2026-10-03'), ('linus', 'Helsinki', 'Widget', '2026-10-01'), ('grace', 'New York', 'Widget', '2026-10-02'), ('grace', NULL, 'Gizmo', '2026-10-04'), ('hedy', NULL, 'Gadget', '2026-10-05'), ('Ada', 'london', 'Gizmo', '2026-10-06');

Step 1: See the duplicates

Without DISTINCT, each order returns its city, so London repeats:

sqlite> SELECT city FROM orders;
city
--------
London
London
Helsinki
New York
NULL
NULL
london

Step 2: Add DISTINCT

sqlite> SELECT DISTINCT city FROM orders;
city
--------
London
Helsinki
New York
NULL
london

The two NULL cities collapse into one NULL row. London and london stay separate, because text is compared with case by default.

Examples

These examples all use the same orders table.

DISTINCT on several columns

Rows are kept when any selected column differs. grace appears twice because her two orders have different cities:

sqlite> SELECT DISTINCT customer, city FROM orders;
customer  city
--------  --------
ada       London
linus     Helsinki
grace     New York
grace     NULL
hedy      NULL
Ada       london

DISTINCT with WHERE and ORDER BY

WHERE filters the rows first, and DISTINCT removes duplicates from what’s left. Then ORDER BY sorts the result:

sqlite> SELECT DISTINCT product FROM orders WHERE ordered_on >= '2026-10-02' ORDER BY product;
product
-------
Gadget
Gizmo
Widget

Count unique values

count(DISTINCT …) counts the different values. NULL isn’t counted, so there are 4 cities, not 5:

sqlite> SELECT count(*) AS order_rows, count(DISTINCT customer) AS customers, count(DISTINCT city) AS cities FROM orders;
order_rows  customers  cities
----------  ---------  ------
7           5          4

Ignore case when removing duplicates

COLLATE NOCASE makes ada and Ada the same. You can also compare a lower-case copy:

sqlite> SELECT DISTINCT customer COLLATE NOCASE AS customer FROM orders;
customer
--------
ada
linus
grace
hedy
sqlite> SELECT DISTINCT lower(city) AS city FROM orders WHERE city IS NOT NULL;
city
--------
london
helsinki
new york

Combine the distinct values into one string

group_concat(DISTINCT …) lists each value once:

sqlite> SELECT group_concat(DISTINCT product) AS products FROM orders;
products
-------------------
Widget,Gadget,Gizmo

Edge cases

Each of these comes from the test run.

DISTINCT or GROUP BY?

For a plain list of values, both give the same rows. Use GROUP BY when you also need a count or total per value:

sqlite> SELECT customer, count(*) AS orders FROM orders GROUP BY customer;
customer  orders
--------  ------
Ada       1
ada       2
grace     2
hedy      1
linus     1

There’s no DISTINCT ON

PostgreSQL’s DISTINCT ON isn’t SQLite syntax:

sqlite> SELECT DISTINCT ON (customer) customer, product FROM orders;
Error: in prepare, near "ON": syntax error
  SELECT DISTINCT ON (customer) customer, product FROM orders;
                  ^--- error here

To keep one row per customer, number each customer’s rows with the row_number() window function, then keep row 1. Here that’s each customer’s latest order:

sqlite> SELECT customer, product, ordered_on FROM (SELECT customer, product, ordered_on, row_number() OVER (PARTITION BY customer ORDER BY ordered_on DESC) AS rn FROM orders) WHERE rn = 1;
customer  product  ordered_on
--------  -------  ----------
Ada       Gizmo    2026-10-06
ada       Gadget   2026-10-03
grace     Gizmo    2026-10-04
hedy      Gadget   2026-10-05
linus     Widget   2026-10-01

Numbers and text that look the same

The docs say “an integer is equal to a floating point number if they represent the same quantity”, but no type conversion happens. So 1 and 1.0 collapse into one row, while the text '1' stays separate:

sqlite> SELECT v, typeof(v) AS type FROM (SELECT DISTINCT v FROM (SELECT 1 AS v UNION ALL SELECT 1.0 UNION ALL SELECT '1'));
v  type
-  -------
1  integer
1  text

DISTINCT and indexes

Without an index, SQLite builds a temporary B-tree to find the duplicates. With an index on the column, it reads the values in order from the index instead:

sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT product FROM orders;
QUERY PLAN
|--SCAN orders
`--USE TEMP B-TREE FOR DISTINCT
sqlite> CREATE INDEX idx_orders_product ON orders(product);
sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT product FROM orders;
QUERY PLAN
`--SCAN orders USING COVERING INDEX idx_orders_product

Conclusion

Use SELECT DISTINCT to list each value once, and count(DISTINCT …) to count them. Add COLLATE NOCASE when case shouldn’t matter. Switch to GROUP BY when you need a number per value, and to row_number() when you need a whole row per group.