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

Question

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.

Answer 1

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