SELECT DISTINCT for only one Column in Google Sheets Query

Let's say I have the following query.

=query (A:C; "SELECT A, B, C")

How can I modify it so that it returns no duplicate A?

In other words, when several rows contain the same A, I want the results to include only one of those rows (the last one). Duplicates in other columns should be allowed.

Here I have found an answer for SQL Server, but I can't figure out how to do this in Google Sheets Query DISTINCT for only one Column

what I have:

A,             B,        C
[email protected], approved, 05/04/2019
[email protected], not set, 05/05/2019
[email protected], refunded, 05/06/2019
[email protected], approved, 05/06/2019
[email protected], approved, 05/07/2019
[email protected], approved, 05/07/2019

what I want:

A,             B,        C
[email protected], refunded, 05/06/2019
[email protected], approved, 05/07/2019

Upvotes: 5

Answers (3)

Galaxy IT

Reputation: 794

As of today, June, 2020, the query syntax has changed a bit, for those who didn't get the formula above, try to replace it with this variant.

== For those who don't know what this is about. Google selection of the table of unique values grouped and sorted by date of row creation

=UNIQUE(FILTER(A:C;MATCH(A:A&C:C;QUERY(QUERY(A:C;"select A, max(C) where A <> '' GROUP BY A label max(C) ''");"select Col1")&QUERY(QUERY(A:C;"select A,max(C) where A<>'' group by A label max(C) ''");"select Col2");0)))

Upvotes: 0

player0

Reputation: 1

you can use SORTN where the 3rd parameter is set to 2:

=SORTN(SORT(A1:C, 3, 1), ROWS(A:A), 2, 1, 0)

Upvotes: 11

Kishan

Reputation: 1810

Try the following formula:

=unique(filter(A:C,match(A:A&C:C,query(query(A:C,"select A,max(C) where A<>'' group by A label max(C) ''"),"select Col1")&query(query(A:C,"select A,max(C) where A<>'' group by A label max(C) ''"),"select Col2"),0)))

Screenshot:

Upvotes: 0

SELECT DISTINCT for only one Column in Google Sheets Query

Answers (3)

Related Questions