returning the number of rows from TOP PERCENT

Greetings.

I want to write a sql query that returns the number of rows from a query that uses top percent. For example,

select top (7) percent object from objecttable

It appears that @.@.rowcount will give the info but it does so as a separate column on each returned row.

Any suggestions would be appreciated.

Thanks.

alan

Try this

select top (7) percent object , count(1) over (partition by 1)

from objecttable order by somecolumn

You are using the new window aggregate ability.

Remember you should always use an order by with TOP

|||

Thanks for the quick reply.

I tried

select top (7) percent objectid , count(1) over (partition by 1)

from bases order by state

and got

(objectid) (no column name)

16 85
19 85
23 85
85 85
38 85
34 85

I'm not sure what '85' represents. When I select and use @.@.rowcount I get '6' in each row. All I really want to do is return '6'. I figure I missed something.

alan

|||@.@.ROWCOUNT gives the number of rows affected by the last statement. So when you use it in the SELECT list you are returning the rows that were affected by the statement before the SELECT. It is not the same as the rows that will be returned by the query. Best is to issue another query "SELECT @.@.ROWCOUNT as rows" after the query or count on the client-side. This will provide the best performance. You could use COUNT with OVER clause but note that has performance implications due to additional computation, sorting etc.|||

This will return the single value 6 (Given your table holds 85 rows)

WITH t1 AS
(SELECT TOP(7) PERCENT * FROM test)
SELECT COUNT(*) FROM t1;

Or this one:

SELECT COUNT(*)
FROM (SELECT TOP(7) PERCENT * FROM test) as t1;

The Execution Plan for Both Queries is the same.

|||Works great. Thanks !|||Works for me. Cool ! Thanks.|||

Or just

select count(1) *7/100

from mytable

|||

Sorry, but the result is not the same (Given the table test holds 85 rows):

SELECT COUNT(1) *7/100 FROM test;

Returns 5

SELECT COUNT(*)
FROM (SELECT TOP(7) PERCENT * FROM test) as t1;

Returns 6

To do it like your hint, the formula must be

SELECT CAST(ROUND(COUNT(1) * 7./100, 1) AS INT) FROM test;

Which returns 6.

There's no performance difference, but i'll prefer the solution which is the easiest to understand.

|||

The answers are different because the TOP will return whole rows i.e. rounds up.

So the answer is something like 5.3