Noticed in our logs that a pretty significant number of the people hitting this post through their favorite search engines are looking for a way to find the top XX% of results in PostgreSQL or MySQL. Like I mentioned in my other post on the subject, however, neither currently can do that natively.

If you're determined to return a % of rows in your query, you can try using a prepared statement. If I wanted to return 30% of the rows in my NERD table, for instance, I could do something like this:

SET @intCount=(SELECT (.3 * COUNT(*)) FROM nerd);

PREPARE STMT FROM 'SELECT * FROM nerd LIMIT ?';

EXECUTE STMT USING @intCount;

No, it's not pretty, but it works... at least in MySQL. I haven't tested that in PostgreSQL, but I'm sure that, if it doesn't, there's a very similar method.

"But Master Justin," you might say. "Why can't I just use that first SELECT statement in my LIMIT clause, like so:"

SELECT * FROM nerd LIMIT (SELECT (.3 * COUNT(*)) FROM nerd);

Very good question, young Padawan. Short answer: MySQL doesn't allow it. The LIMIT clause just can't use an expression. Tried it several times while editing this post and failed miserably. Even if you try to CAST/CONVERT your second SELECT statement into an integer of some kind, it still doesn't like it.

One last thing... I can't get this method to work in the MySQL Query Browser, either. Works great in the command line, though. Go figure.

Would love to hear from anybody else out there with a favorite way of doing this, or from someone who can test/tweak the above for PostgreSQL.