How to get non grouped-by columns in SQL statement

Question

In MySQL, I can do the following query:

select first_name from `table` group by age

However, if I do something similar in BigQuery, I get the following error:

SELECT list expression references column first_name which is neither grouped nor aggregated

How would we write the equivalent mysql aggregation query in BigQuery?

Answer 1

In BigQuery, you should be using window functions:

select t.*
from (select t.*, row_number() over (partition by age order by age) as seqnum
      from t
     ) t
where seqnum = 1;

This works in MySQL 8+ as well.

Answer 2

you have to put all the selected column in group by also

 select first_name, last_name from 
`table` group by age,first_name, last_name

btw before 8.0 version of mysql it was allow but from 8.0 it is not allow

but in you query there is no aggregate function so you can use distinct rather than group by

select distinct first_name, last_name from 
    `table`

Answer 3

Below is for BigQuery Standard SQL and as simple as below

#standardSQL
SELECT ANY_VALUE(first_name) first_name
FROM `project.dataset.table`
GROUP BY age

As you can see you were missing just aggregation function - it can be any - MAX, MIN, etc. I've chosen ANY_VALUE as an example

You can test, play with above using some simplified dummy data like in example below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Thomas' first_name, 25 age UNION ALL
  SELECT 'Mike', 25 UNION ALL
  SELECT 'Thomas', 30 UNION ALL
  SELECT 'Mark', 40
)
SELECT ANY_VALUE(first_name) first_name
FROM `project.dataset.table`
GROUP BY age

with result

Row first_name   
1   Thomas   
2   Thomas   
3   Mark