Solved: How to get 'unique' rows in a Google Sheets QUERY(), ignoring a specific column when factoring uniqueness?

📌 The Problem Explained

In Google Sheets, the QUERY() function’s SELECT DISTINCT clause only returns rows that are unique across all selected columns. However, real-world data is often “dirty.”

Imagine you have a sales log with columns for Customer Name, Email, and Timestamp. If a customer signs up twice, their Timestamp will be different. If you try to use QUERY() to get a list of unique customers, it will return both entries because the Timestamp column makes the rows technically different. You need a way to tell Google Sheets: “Give me unique rows based on Name and Email, but ignore the Timestamp when deciding what counts as a duplicate.”

💡 The Quick Solution

The most efficient way to handle this is to wrap a SORTN() function inside your QUERY(). This filters the uniqueness before the Query processes the data.

=QUERY(SORTN(A2:C, 9^9, 2, A2:A&B2:B, TRUE), "SELECT * WHERE Col1 IS NOT NULL")

(Note: In this example, columns A and B are used for uniqueness, while column C is the one being ignored/kept as-is for the first instance found.)

🛠️ Step-by-Step Breakdown

To implement this, you must bypass the standard range and create a “virtual array” that SORTN cleans first.

Step	Action	Description
1. Define Range	A2:C	Select your entire data range, including the column you want to ignore.
2. Set Limit	9^9	This tells SORTN to return an “infinitely” high number of rows (essentially all of them).
3. Tie Mode	2	This is the “Magic Number.” Mode 2 removes duplicates based on the columns specified next.
4. Unique Key	A2:A&B2:B	This concatenates the columns you want to check for uniqueness. It ignores any columns not listed here.
5. Sort Order	TRUE	Determines if the remaining unique rows are sorted ascending (TRUE) or descending (FALSE).
6. QUERY Wrap	QUERY(..., "SELECT...")	Now that the data is unique, run your query. Use Col1, Col2 references instead of A, B.

⚠️ Common Mistakes to Avoid

• Using A, B, C in QUERY: When you wrap a function like SORTN or UNIQUE inside a QUERY, you can no longer use lettered columns (e.g., SELECT A). You must use the index notation (e.g., SELECT Col1, Col2).
• The “Distinct” Misconception: Do not rely on SELECT DISTINCT in the Query string itself if you are including the “ignored” column in your results. If that column has different values, DISTINCT will always show both rows.
• Header Confusion: SORTN does not always handle headers gracefully within the formula. It is best to start your range at row 2 (A2:C) and manually type your headers in the row above or use Data > Create a filter to manage them.