Adding a row number - with max() - BigQuery

Question

I found this solution here which works real nice, but I need to add a max() function to my results so that only the maximum visitnumber is reported, whilst still grouped by fullvisitorID with descending row number

This is how my query currently looks,

SELECT 
row_number () over() row_number,
fullvisitorID,
visitnumber,
FROM (
SELECT fullvisitorID, visitnumber, 
FROM [data]
GROUP BY fullvisitorID,visitnumber
ORDER BY visitnumber DESC
)

Any help on how to introduce the max() function gratefully appreciated.

Current result:
Row row_number  fullvisitorID   visitnumber  
1   1   7798230386693640077 15187    
2   2   7798230386693640077 15186    
3   3   7798230386693640077 15185    
4   4   6990057712104307809 15133    
5   5   1693270353484553172 12519    
6   6   1693270353484553172 12518

Desired result:
Row row_number  fullvisitorID   visitnumber  
1   1   7798230386693640077 15187    
2   2   6990057712104307809 15133
3   3   1693270353484553172 12519 
4   4   2117072763989389740 11607
5   5   2336272987018271381 10487    
6   6   1563891584913132229  9927

can you show result and desire result ? – krishn Patel Apr 11 '17 at 10:42 — krishn Patel, Apr 11 '17 at 10:42

score 1 · Answer 1 · answered Apr 11 '17 at 14:09

Try this:

SELECT 
   row_number () over() row_number,
   fullvisitorID,
   maxVisitNumber       
FROM (
        SELECT fullvisitorID, max(visitnumber) as maxVisitnumber,
        FROM [data]
        GROUP BY fullvisitorID
        ORDER BY maxVisitnumber DESC
)

That is, use the subquery to compute the maximum visitNumber for each fullVisitorId before getting the row number.

score 0 · Accepted Answer · answered Apr 11 '17 at 12:48

Try below

SELECT
  ROW_NUMBER() OVER(ORDER BY visitnumber DESC) rownumber,
  fullvisitorID,
  visitnumber
FROM (
  SELECT 
    ROW_NUMBER() OVER(PARTITION BY fullvisitorID ORDER BY visitnumber DESC) pos,
    fullvisitorID,
    visitnumber,
  FROM (
    SELECT fullvisitorID, visitnumber, 
    FROM [data]
    GROUP BY fullvisitorID,visitnumber
  )
)
WHERE pos = 1
-- ORDER BY visitnumber DESC

Adding a row number - with max() - BigQuery

2 Answers2