SQL query to split table into 3 parts and fetch top 5 among them-CodePudding

I have a table consisting of name, values. I want to split this into 3 parts, upper, middle and lower and get the top 5 values in all three. Is there a way to do so?

EDIT 1: The table having 45 entries for example will look like.

name  | value
a1    | v1
a2    | v2
a3    | v3
...
a43   | v43
a44   | v44
a45   | v45

And I want a resulting table as

name  | value
a1    | v1
a2    | v2
a3    | v3
a4    | v4
a5    | v5
a16   | v16
a17   | v17
a18   | v18
a19   | v19
a20   | v20
a31   | v31
a32   | v32
a33   | v33
a34   | v34
a35   | v35

CodePudding user response：

Total number of rows / 3 will give you the size of partition; and (row number - 1) % size of partition will give you the row number within the partition. So to get 5 rows per partition you would:

with cte as (
    select *, row_number() over (order by name) as rn, count(*) over () as c
    from t
)
select *
from cte
where (rn - 1) % (c / 3) <= 4

CodePudding user response：

If your database supports window functions use NTILE() to split the table in as many parts as you want and with ROW_NUMBER() keep as many rows as you want from each part:

SELECT name, value
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY part ORDER BY name) rn
  FROM (
    SELECT *, NTILE(?1) OVER (ORDER BY name) part
    FROM tablename
  ) t  
) t
WHERE rn <= ?2;

Change ?1 to the number of parts that you want to split the table and ?2 to the number of rows that you want from each part.

See the demo.