1

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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Colin
  • 23
  • 1
  • 5
  • http://stackoverflow.com/questions/4741239/select-top-x-or-bottom-percent-for-numeric-values-in-mysql http://stackoverflow.com/questions/4741239/select-top-x-or-bottom-percent-for-numeric-values-in-mysql – Pரதீப் Dec 10 '16 at 14:04
  • 2
    Please tag your question with the database you are using. – Gordon Linoff Dec 10 '16 at 14:04

1 Answers1

0

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786