CODEWITHSUNDEEP
amazon-redshift
Tomasz Tybulewicz
Tomasz Tybulewicz

Reputation: 8647

ROW_NUMBER() in Redshift to select biggest row from each group?

I need to select one row from each group based on COUNT(1) field.

In other databases I'd use ROW_NUMBER() function, which in redshift is unsupported yet.

Upvotes: 2

Views: 8626

Answers (1)

Tomasz Tybulewicz
Tomasz Tybulewicz

Reputation: 8647

The answer is to use a SUM(1) OVER(PARTITION BY group_field ORDER BY order field ROWS UNBOUNDED PRECEDING) construct like that: 

SELECT id,
       name,
       cnt
FROM
  (SELECT id,
          name,
          count(*) cnt,
          sum(1) over (partition BY id ORDER BY cnt DESC ROWS UNBOUNDED PRECEDING) AS row_number
   FROM table
   GROUP BY id,
            name)
WHERE row_number = 1
ORDER BY name

Upvotes: 6

Related Questions