2

Possible Duplicate:
Select TOP X (or bottom) percent for numeric values in MySQL

How would I write this query in MySQL??

SELECT TOP 50 PERCENT * FROM Persons

The number of entries in the table will be constantly changing so I need to select by percentage rather than a fixed number.

Community
  • 1
  • 1
Tom
  • 12,776
  • 48
  • 145
  • 240

1 Answers1

8

This is actually a duplicate question, but I now have a better, single-pass, fast, answer:

SELECT *
FROM
(
    SELECT tbl.*, @counter := @counter +1 counter
    FROM (select @counter:=0) initvar, tbl
    ORDER BY ordcolumn
) X
WHERE counter <= (50/100 * @counter)
ORDER BY ordcolumn;
Mohamed23gharbi
  • 1,710
  • 23
  • 28
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 2
    Actually it's two passes (one from table, one from derived), but still O(N) rather than O(N^3), the accepted answer in the linked question. OTOH, my original answer in the duplicate works slightly faster, although it requires a batch rather than a single query. – RichardTheKiwi Apr 02 '11 at 10:01