Question 88895 in PostgreSQL Question posted in
The official documentation can be found here.

Athena top 1 result from each group – Postgresql

is there any way to implement top 1 result from each group without using ROW_NUMBER?
table_a below has already 1 billion rows, so I want to use the most efficient way.

And the below query is what I’m using.

SELECT *
FROM   (
        SELECT ROW_NUMBER() OVER (PARTITION BY column_a, column_b, column_c ORDER BY column_d) AS row_num,
               *
        FROM   table_a
        )
WHERE  row_num = 1
Tags:

2

Answers


  1. 0

    This can be done using group by and the aggregate function min() :

    SELECT column_a, column_b, column_c, min(column_d) as top1
FROM table_a
group by column_a, column_b, column_c
    Login or Signup to reply.
  2. 0

    An alternative would by distinct on. However distinct on is a Postgres extension and not a SQL standard.

    select distinct on (column_a, column_b, column_c) 
       column_a, column_b, column_c, column_d as top1
  from table_a
 order by column_a, column_b, column_c, column_d;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search