BigQuery: Return First Value from Different Groups in a Group By

Question

I am currently having a problem with a Standard SQL query. I have a list of emails where every email can have multiple functions. See the example below on how the table looks like.

Email                         Function
[email protected]               engineer
[email protected]               specialist
[email protected]                analyst
[email protected]                tester
[email protected]                manager
[email protected]             intern

What I want is a query that returns every email once with the first function it finds. So the above table should return the following:

Email                         Function
[email protected]               engineer
[email protected]                analyst
[email protected]             intern

How do I do this?

What I have right now is a simplified version of the query.

SELECT Email, Function
FROM database
GROUP BY Email, Function

The issue is here is that I have to put both Email and Function in the GROUP BY. If I only put Email in the Group By the query cannot run even though I only want the query to GROUP BY Email.

Thanks!

Answer 1

There is no such thing as the "first" function, because SQL tables represent unordered sets -- especially in a designed-from-the-ground-up parallel database such as BigQuery.

You need to use some sort of aggregation function.

A simple one is any_value():

SELECT Email, ANY_VALUE(Function)
FROM database
GROUP BY Email;

If you have another column that specifies the ordering, then it can be used to fetch the function associated with the minimum value of that column.