CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user2569831
user2569831

Reputation:

PLPGSQL - stored procedure to get a set of rows with count

I am using PostgreSQL.

I need stored procedure using PLPGSQL language that will return table (SET OF RECORDS) containing count of top 2 and bottom 2 results from my_table.

For example:

my_table

id       value
1          a
2          a
3          a
4          b
5          b
6          c
7          c
8          e
9          f
10         g
11         g
12         g
13         g
14         h
15         h

Returns:

count           value
  4               g
  3               a
  1               e
  1               f

Thank you

Upvotes: 0

Views: 533

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can use window functions with aggration

select v.value, v.cnt
from (select value, count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum_desc,
             row_number() over (order by count(*) asc) as seqnum_asc
      from t
      group by value
     ) v
where seqnum_desc <= 2 or seqnum_asc <= 2;

Note: In the case of ties -- particularly likely at the bottom end -- this returns arbitrary values with the same count. You can adjust for this using rank() or dense_rank(), depending on what you want in this case.

Upvotes: 1

Related Questions