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.

Would you care to add 1) table structure 2) sample data, and 3) the expected result? — Jim Jones, Sep 17 '20 at 10:40
https://stackoverflow.com/questions/54957288/in-postgresql-how-to-select-top-n-percent-of-rows-by-a-column — a_horse_with_no_name, Sep 17 '20 at 10:56
@Jim Jones, Its a genric question. I think no need of sample data,table structure. — shoyab, Sep 17 '20 at 12:09

score 3 · Accepted Answer · answered Sep 17 '20 at 11:00

3

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

answered Sep 17 '20 at 11:00

GMB

As a side note: internally the `fetch first 50 percent` is translated to `select * from (select row_number() over (order by id) as rn, count(*) over () as total) where rn <= (total * 50/100)` – a_horse_with_no_name Sep 17 '20 at 11:32
@GMB ,Your query is working. Will you help me ,how to write same query in Data warehouse. – shoyab Sep 17 '20 at 12:13
why wouldn't that same query work in a data warehouse? – thatjeffsmith Sep 17 '20 at 12:26
@thatjeffsmith,This keywords are not available in Data warehouse like hive. – shoyab Sep 17 '20 at 12:30
Keywords are not genric in all sql like oracle,MS sql, postgresql,etc., I am working on hive, But facing issue to replicate the same. – shoyab Sep 17 '20 at 12:31
then mark your question as for Hive - many data warehouses are implemented in oracle and the same SQL works in any of them – thatjeffsmith Sep 17 '20 at 12:32
also you specified ANSI, and the fetch first is ANSI SQL syntax https://dba.stackexchange.com/a/30455/271 – thatjeffsmith Sep 17 '20 at 12:33
@thatjeffsmith, Every data warehouse allow ANSI sql syntax, to get generic syntax i have marked ANSI. – shoyab Sep 20 '20 at 10:52
Hive gives you a SQL-like interface...not sure it has a complete ANSI SQL implementation – thatjeffsmith Sep 20 '20 at 12:02

1 Answers1