How To Get Non Grouped-by Columns In Sql Statement (similar To In Mysql)

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 expres

Solution 1:

Below is for BigQuery Standard SQL and as simple as below

#standardSQL
SELECT ANY_VALUE(first_name) first_name
FROM `project.dataset.table`
GROUPBY 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 UNIONALLSELECT'Mike', 25UNIONALLSELECT'Thomas', 30UNIONALLSELECT'Mark', 40
)
SELECT ANY_VALUE(first_name) first_name
FROM `project.dataset.table`
GROUPBY age

with result

Row first_name   
1   Thomas   
2   Thomas   
3Mark

Solution 2:

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

select first_name, last_name from 
`table` groupby 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

selectdistinct first_name, last_name from 
    `table`

Solution 3:

In BigQuery, you should be using window functions:

select t.*from (select t.*, row_number() over (partitionby age orderby age) as seqnum
      from t
     ) t
where seqnum =1;

This works in MySQL 8+ as well.