How to get a dynamic top number of rows?

Question

I want to get the top #nr_of_rows - (int)(#nr_of_rows / 10 - 1) of a table. So like if you have 63 rows, it would return 3 rows, 90 rows would return 10, 85 would return 5, etc.. Of course this can be done in two queries, just wondering if it can be done in one query.

If you guys know of a straight sql query, it would be great but ideally I would like a linq to sql answer.

Answer 1

I don't get how your formula maps to the examples. 90 - (int)(90 / 10 - 1) is 82. But the basic concept is:

select * from foo limit (select count() - (select (count() / 10 - 1)));

Just hope you have a decent query optimizer.

Answer 2

SELECT TOP 10 PERCENT * FROM Table

or

DECLARE @pct AS INT
SELECT @pct = (COUNT(*) / 10 )-1 FROM Table

SELECT TOP (@pct) * FROM Table

or

DECLARE @pct AS INT
SELECT @pct = (COUNT(*) / 10 )-1 FROM Table

SET ROWCOUNT @pct

SELECT * FROM Table

I'm pretty weak on Linq to Sql but I don't think you can't avoid 2 lookups, since you're still going to need to subquery to get the count(*), the sql generated would still have the subquery which this similiar post seems to validate http://stackoverflow.com/questions/470339/linq-version-of-top-percent