SQL Limit Function

[macuser154]

I don't really know where this would go. But does anyone know how I can use the limit parameter in SQL to only bring back 25% of the total amount of records?

 

[angelwatt]

I'd probably just create a temp variable that calculates it

SET @quarter = (SELECT COUNT(*) FROM mytable) / 4;
SELECT * FROM mytable LIMIT ROUND(@quarter);

Haven't tested this though.

 

[belvdr]

I'd probably just create a temp variable that calculates it

SET @quarter = (SELECT COUNT(*) FROM mytable) / 4;
SELECT * FROM mytable LIMIT ROUND(@quarter);

Haven't tested this though.

I wouldn't recommend that on a very large table. Talk about a resource hog with two full table scans.

I'd recommend limiting it to a distinct number. Using percentages, if the table has 100 rows, then you pull back 25. If it has 1,000,000, then you're pulling back 250,000, which is most likely not what the user wanted.

 

[macuser154]

I wouldn't recommend that on a very large table. Talk about a resource hog with two full table scans.

I'd recommend limiting it to a distinct number. Using percentages, if the table has 100 rows, then you pull back 25. If it has 1,000,000, then you're pulling back 250,000, which is most likely not what the user wanted.

This limiter would be used on a View, which tends not to have many records in it. Would that help keep the resource usage down?

 

[belvdr]

This limiter would be used on a View, which tends not to have many records in it. Would that help keep the resource usage down?

A view is transparent and holds no actual data, so no it won't help. How many rows does this view contain?