SELECT DISTINCT for only one Column in Google Sheets Query

Question

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
lucas@abc.com, approved, 05/04/2019
lucas@abc.com, not set, 05/05/2019
lucas@abc.com, refunded, 05/06/2019
john@xyz.com, approved, 05/06/2019
john@xyz.com, approved, 05/07/2019
john@xyz.com, approved, 05/07/2019

what I want:

A,             B,        C
lucas@abc.com, refunded, 05/06/2019
john@xyz.com, approved, 05/07/2019

player0 · Accepted Answer · 2019-07-23T05:24:39.377

10

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

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

edited Jul 23 '19 at 05:24

answered Jul 22 '19 at 14:02

player0

124,011
12
67
124

I have changed 1 for 0 at third parameter of SORT function and now it returns what i was expecting (shows only the last row), thanks! – Lucas Reis Aug 19 '19 at 18:17
1

@425nesp sure. we SORT range A:C by 3rd column ascending (1). then we use SORTN and we return all possible ROWS of A column. then we use 2nd mode of SORTN to group data by 1st column but now descending (0) – player0 Apr 18 '21 at 21:22

score 0 · Answer 2 · answered Jul 22 '19 at 18:55

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:

score 0 · Answer 3 · answered Jun 21 '20 at 10:49

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)))

SELECT DISTINCT for only one Column in Google Sheets Query

3 Answers3