CODEWITHSUNDEEP
sqlpostgresqlunion-all
Lay
Lay

Reputation: 249

Select two distinct sets of rows for one column in one statement?

I have a table with 2 columns:
name, percentage

I have a 100 rows in this table, and want to make a query that selects the 5 rows with the smallest percentage value and the 5 rows with the largest percentage value.

Normally I would do this with limit and offset but it will select only one of the outcome groups I seek. I wonder if there is a way of selecting both.

I have been looking for a solution for a while, and I thought about FETCH, but I don't really succeed in using right.

Upvotes: 2

Views: 124

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656301

UNION ALL

(
SELECT name, percentage
FROM   tbl
ORDER  BY percentage
LIMIT  5
)
UNION ALL
(
SELECT name, percentage
FROM   tbl
ORDER  BY percentage DESC
LIMIT  5
);

You need parenthesis, to apply ORDER BY and LIMIT to nested SELECT statements of a UNION query. I quote the manual here:

ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.

UNION (without ALL) would remove duplicates in the result. A useless effort if you don't expect dupes.

Subquery with row_number()

SELECT name, percentage
FROM  (
   SELECT *
        , row_number() OVER (ORDER BY percentage) AS rn_min
        , row_number() OVER (ORDER BY percentage DESC) AS rn_max
   FROM   tbl
   ) x
WHERE rn_min < 6
OR    rn_max < 6;

This collapses duplicates like UNION would. Performance will be similar, probably a bit slower than the first one.

Either way, order by additional columns to break ties in a controlled manner. As is, you get arbitrary rows from groups of peers sharing the same percentage.

Upvotes: 1

Related Questions