9

Say I define an alias 'count' in my Select Query and I want to limit the amount returned to count / 5 (or 20% of the table).

How can I do this? Mysql doesn't seem to take anything but integers, not functions.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502

2 Answers2

12

Correct. The LIMIT clause takes an offset and a count of rows, not a percentage. You're thinking of Microsoft SQL Server, which supports SELECT TOP 20 PERCENT ... (note that neither LIMIT or TOP are specified in standard SQL).

I would do this in two queries:

SELECT COUNT(*) FROM MyTable WHERE ...conditions...

SELECT * FROM MyTable WHERE ...conditions... ORDER BY ...order... LIMIT ?

Replace the parameter ? with the count / 5.

You don't have to solve every problem in a single query.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Just to add, I believe that running these two queries does *not* cause significant overhead, since MySQL should cache the result of the first query and the second one will actually run a lot faster. – DisgruntledGoat Oct 16 '09 at 11:28
5

the LIMIT clause can takes 2 arguments and must be integers constants.

you can try something like this

SET @skip=1; SET @numrows=(select count(*) div 5 from tbl );
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
RRUZ
  • 134,889
  • 20
  • 356
  • 483