CODEWITHSUNDEEP
sqldistinct-values
Kaustubh Ghole
Kaustubh Ghole

Reputation: 587

How to use distinct on single column and return multiple other columns in bigquery?

I have a bigquery table which has duplicate values and I would like to use distinct operator to remove those duplicates. But not getting expected output after executing below query.

Here is the query: 

SELECT DISTINCT
    customerRefNo,
    custType,
    executionDate,
    Unit
FROM `myproject.mydataset.mytable`

In my table there are duplicates of customerRefNo and would like to remove those. Any suggestion from anyone ?

Upvotes: 1

Views: 4762

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

Based on your comments, the following GROUP BY query might be along the lines of what you want:

SELECT
    customerRefNo,
    custType,
    MAX(executionDate) AS executionDate,
    Unit
FROM `myproject.mydataset.mytable`
GROUP BY
    customerRefNo,
    custType,
    Unit;

Upvotes: 2

Related Questions