BigQuery - Select only first row in BigQuery

Question

I have a table with data where in Column A I have groups of repeating Data (one after another).

I want to select only first row of each group based on values in column A only (no other criteria). Mind you, I want all corresponding columns selected also for the mentioned new found row (I don't want to exclude them).

Can someone help me with a proper query.

Here is a sample: SAMPLE

Thanks!

Answer 1

You can now use qualify for a more concise solution:

select
   *
from
   your_dataset.your_table
where true
qualify ROW_NUMBER() OVER(PARTITION BY columnA ORDER BY columnA) = 1

Answer 2

Add LIMIT 1 at the end of the query

something like

SELECT name, year FROM person_table ORDER BY year LIMIT 1

Answer 3

#standardSQL
SELECT row.*
FROM (
  SELECT ARRAY_AGG(t LIMIT 1)[OFFSET(0)] row
  FROM `project.dataset.table` t
  GROUP BY columnA
) 

Answer 4

you can try smth like this:

#standardSQL
SELECT
   * EXCEPT(rn)
FROM (
   SELECT
   *,
   ROW_NUMBER() OVER(PARTITION BY columnA ORDER BY columnA) AS rn
FROM
   your_dataset.your_table)
WHERE rn = 1

that will return:

Row   columnA       col2       ...   
1     AC1001        Z_Creation   
2     ACO112BISPIC  QN
...