SQL to select the middle third of all values in a column in PostgreSQL

Question

Suppose I have a column of heights -- how can I select all and only those height values that are neither in the top 30% of values nor the bottom 30% of values.

UPDATE:

I'd like the answer for PostgreSQL (or, failing that, MySQL -- I'm using Rails).

Answer 1

For SQL Server 2005 +

SELECT
    *
FROM
    MyTable M
EXCEPT
SELECT
    *
FROM
    (SELECT TOP 30 PERCENT
        *
    FROM
        MyTable M
    ORDER BY
        Height
    UNION ALL
    SELECT TOP 30 PERCENT
        *
    FROM
        MyTable M
    ORDER BY
        Height DESC) foo

Answer 2

for sql server 2005+ you should use the NTILE() function for this.

SELECT *
FROM   (
         SELECT ntile(3) over(order by AddressId) as Percentile, *
         FROM   (
                SELECT top 100 *
                FROM   Person.Address
           ) t
       ) t
where Percentile = 2

Answer 3

WITH cte AS (
 SELECT *, NTILE(100) OVER (ORDER BY column) as rank
 FROM table)
SELECT * FROM cte WHERE rank BETWEEN 30 and 70

Answer 4

You're asking for PostgresSQL, and that doesn't support NTITLE or TOP X PERCENT.

Without either of those, I can think of a query like this retrieve the middle rows:

select *
from MyTable
where height not in (
    select Height from MyTable order by Height desc 
    limit ((select count(*) from MyTable)*0.3)
    union
    select Height from MyTable order by Height
    limit ((select count(*) from MyTable)*0.3)
)

Now, I'm not sure if PostgresSQL supports a limit calculated in a subquery, and I don't have a PostgresSQL database near to try it.

Answer 5

Hi,

Postgres only accepts contants in limit clause. So the solution above does not work.

Your select is something like this:

SELECT *
  FROM (SELECT T.HEIGHT, 
               -- this tells us the "ranking" of each row 
               -- by counting all the heights that are small than 
               -- height in the that row
               (SELECT COUNT(*) + 1
                  FROM <table> T1 
                 WHERE T1.HEIGHT < T.HEIGHT
               ) AS RANK,
               -- this tells us the count of rows in the table
               (SELECT COUNT(*) 
                  FROM <table> T1
               ) AS REC_COUNT
          FROM <table> T
         ORDER BY T.HEIGHT
       ) T
 -- now just list rows wich ranking is between (not top30) and (not bottom30)
 WHERE T.RANK BETWEEN (T.REC_COUNT*0.30) AND (T.REC_COUNT*0.70)

This is gonna work in any database what accepts subselects (subqueries).

This does not treat equalties in "heights", but it could be done using primary key

SELECT COUNT(*) + 1
  FROM <table> T1 
 WHERE (T1.HEIGHT < T.HEIGHT)
    OR (T1.HEIGHT = T.HEIGHT and T1.PK_FIELD < T.PK_FIELD)

Regards.