MYSQL subquery in a LIMIT

Question

I was wondering if it's possible to use a subquery inside a LIMIT.

The reason why I'd like to use this, is to return 20% (1/5th) of the best buying customers.

For instance (though this clearly doesn't work):

SELECT id, revenue
FROM customers
ORDER BY revenue DESC
LIMIT (SELECT (COUNT(*) / 5) FROM customer) 

Is there a way to make a subquery in a limit, or return 20% in a different way?

Answer 1

A typical way of doing this using ANSI SQL is with window functions:

SELECT id, revenue
FROM (SELECT c.*,
             ROW_NUMBER() OVER (ORDER BY revenue DESC) as seqnum,
             COUNT(*) OVER () as cnt
      FROM customers
     ) c
WHERE seqnum <= cnt * 0.2
ORDER BY revenue DESC;

Most databases support these functions.

MySQL is one of the few databases that do not support window functions. You can use variables:

SELECT id, revenue
FROM (SELECT c.*, (@rn := @rn + 1) as rn
      FROM customers c CROSS JOIN
           (SELECT @rn := 0) params
      ORDER BY c.revenue DESC
     ) c
WHERE rn <= @rn / 5;  -- The subquery runs first so @rn should have the total count here.