How to find Min and Max rows from a table including all columns from postgresql

user_7940365 0
Viewed 1406

I have a view select c1,c2,count from table and it will give result below.

enter image description here

I want to fetch the entire row of maximum and minimum count's value and that should return only two rows with max and min count like below.

enter image description here

How to do it?

greatest-n-per-group window-functions postgresql sql
2 Answers

The quickest way is probably a union:

(
  select c1, c2, count 
  from the_table
  order by count 
  limit 1
)
union all
(
  select c1, c2, count 
  from the_table
  order by count desc
  limit 1
)

Usually the individual statements in a UNION, don't need parentheses, but as we want an order by on each of them, they are needed.

Another option would be join against a derived table:

select t1.*
from the_table t1
  join (
    select min(count) as min_count, 
           max(count) as max_count
    from the_table
  ) mm on t1.count in (mm.min_count, mm.max_count)

But I doubt that this will be faster.

user_3303150

I would recommend window functions:

select *
from (
    select t.*,
        row_number() over(order by count) rn_asc,
        row_number() over(order by count desc) rn_desc
    from mytable t
) t
where 1 in (rn_asc, rn_desc)
order by count

This requires scanning the table only once (as opposed to union all or join).

user_106767160
Related