How to get row count from an inner query

Hi All,

I have the following SQL query:

select temp.emp_id, temp.rownum

from

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

) temp

where temp.rownum <=10

group by temp.emp_id

I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.

Hi,

I wrote something similar in here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2188269&SiteID=1

But the problem may be, that the Number of Rows in the outer Statement dosent match to the number of rows in the inner Statement.

Best Regards

Raimund

|||

How about this,

Code Block

with temp

as

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

)

,countfinder

as

(

select count(*) as [rowcount] from temp

)

select temp.emp_id, temp.rownum, (select [rowcount] from countfinder) [totalrowcount] from temp

where temp.rownum <=10 group by temp.emp_id

|||Brilliant.....this was a very novel way of doing it. Thank you very much. I will try out the same in my implementation.