Deduplicate on a particular column within a Google Sheets query() formula

Question

I'm trying to deduplicate based on the values in a single column when using Google Sheets' query() formula.

query('Data'!A2:D, "select A, B, C, D")

In this example, I'd like to pull out the first instance of each pet location -- basically, the data deduplicated by the Location in column C. So something like:

query('Data'!A2:D, "select A, B, C, D where C is unique")

Is there any way to do this gracefully within Google Sheets?

Example sheet with desired output here:

https://docs.google.com/spreadsheets/d/10lvghkMgw1eOLeUp0TmyWzohWOGhQbZisNwrIujCaXA/edit?usp=sharing

Answer 1

use:

=SORTN(A2:D, 9^9, 2, 3, 0)

---

9^9 = all rows
2 = grouping mode
3 = column number
0 = sorting

Answer 2

Try this:

=ArrayFormula({"Name","Type","Location","Instance Date";VLOOKUP(UNIQUE(FILTER(Data!C2:C,Data!C2:C<>"")),{Data!C2:C,Data!A2:D},{2,3,4,5},FALSE)})

Essentially, this formula sets up your headers, then runs the list of unique locations through a VLOOKUP using a virtual array formed from the locations appended in front of all other data, returning all other data. What you wind up with is first match.

NOTE: Your date returns may come through as raw data (i.e., numbers in the 40,000 range); if so, just format that column in the date format of choice.