CODEWITHSUNDEEP
oracle-databasepostgresqlsql-order-bygreatest-n-per-groupsql-limit
shoyab
shoyab

Reputation: 13

How to write SQL query for extracting 50 percent of records from a table?

How to retrieve 50% of records in ANSI SQL. In MS SQL Server we have Top with percent. But I want to get in Oracle and PostgreSQL.

Upvotes: 1

Views: 2356

Answers (1)

GMB
GMB

Reputation: 222482

In Postgres, one option uses percent_rank(). Assuming that id is your ordering column:

select *
from (select t.*, percent_rank() over(order by id) prn from mytable t) t
where prn <= 0.5

This would also work in Oracle, but for that database I would prefer a fetch clause:

select *
from mytable t
order by id
fetch first 50 percent rows only

Upvotes: 4

Related Questions