BigQuery/SQL: Select first row of each group

Question

I am trying to select the first row of each group. Eg: below table I would want to keep product for www/edf/ and cate for www/abc/.

I have tried multiple ways, eg: ROW_NUMBER() OVER(PARTITION BY [...]) but somehow not getting expected outputs. The challenge for me here is that category does not have numbers as values, otherwise I can filter it down using max or min.

SELECT landing_page,   ROW_NUMBER  OVER ( PARTITION BY landing_page ORDER BY Page_Type DESC) AS ROW_NUM
from `xxxx.TEST.draft`

However I got this error: OVER keyword must follow a function call

Appreciate any help!

Landing_page	Page_type
www/edf/	product
www/edf/	home
www/abc/	cate
www/abc/	home

There needs to be some column(s) which provide the ordering logic for knowing which "first" record to retain per group. Sans this, your question can't really be answered. — Tim Biegeleisen, Jan 20 '22 at 04:09
If you have tried `ROW_NUMBER()` and didn't get desired output, then please show your current code and describe what is wrong with its output. Because`ROW_NUMBER()` is a general way to select N rows per group — astentx, Jan 20 '22 at 05:02

score 2 · Accepted Answer · answered Jan 20 '22 at 16:38

2

I believe you are looking for the function [FIRST_VALUE][1]?

SELECT 
    landing_page,   
    FIRST_VALUE(URL)  
        OVER ( PARTITION BY landing_page ORDER BY Page_Type DESC) AS first_url
FROM `xxxx.TEST.draft`

answered Jan 20 '22 at 16:38

Cylldby

1,783
1
4
17

thank you @Cylldby ! I also tried using row_number() and it worked. I posted my query below if you're interested. – dalehereques Jan 20 '22 at 17:52

score 0 · Answer 2 · answered Jan 20 '22 at 16:49

I answered my own question, sharing the code in case anyone needs:

SELECT session_id,landing_page, Page_Type, ROW_NUMBER ()  OVER (PARTITION BY session_id,landing_page ORDER BY Page_Type DESC) AS ROW_NUM
from `xxx._TEST.draft`
order by session_id desc

BigQuery/SQL: Select first row of each group

2 Answers2

Linked