CODEWITHSUNDEEP
sqlgoogle-bigquery
GiltLorn
GiltLorn

Reputation: 11

How can I return the SECOND GREATEST value in Google BigQuery

I have a CASE statement within an ON clause:

ON A.month = CASE WHEN (B.month = GREATEST(B.month)) 
             THEN B.month - 1  
             ELSE B.month 
             END

It will function until the year rolls over.

Is there a way to return the second GREATEST value instead of using B.month - 1?
Something similar to the Excel function LARGE(*array*, 2)?

Subqueries don't seem to work within ON clauses.

Upvotes: 0

Views: 2246

Answers (1)

Vibhor Gupta
Vibhor Gupta

Reputation: 699

Please try following SQL to get second largest value:-

SELECT *
FROM (
  SELECT Coll, 
         row_number() over (order by Coll desc) as row_num
  FROM `project.dataset.table`
) t
WHERE row_num = 2

Upvotes: 1

Related Questions