I have a view select c1,c2,count from table and it will give result below.
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.
How to do it?
Add a comment
|
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.
2 Comments
Johnson Anthony
the above can be helpful for single set. for eg. I have one more column namely C3 and C3 is not unique for all rows. But it will repeat for few rows. In that case I want to group rows by Column C3 and find min max for each set of C3. Is that possible???
@JohnsonAnthony: please do not extend the scope of your question once you have an answer. Have a look at the current answers for greatest-n-per-group most probably this was already answered.
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).
1 Comment
If there is an index on
count the order by ... limit 1 is usually faster. The window function requires to read all values to apply the sorting. plan for union plan for derived table plan for window function - on a table with a million rows and an index on count