Link to home
Create AccountLog in
Avatar of rafaelrgl
rafaelrgl

asked on

how do i return the number of rows selected

hi,  how do i return the number of rows selected without performing another query, lets say


select t1.name, getnumbersofrows from tb_users where ......

how do i return on this column the numbers of rows that were selected without making another select on the table.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of lmfs
lmfs

SELECT COUNT (fieldname) as Field1

from tablename

or if you want to count distinct rows:

SELECT COUNT (DISTINCT fieldname) as Field1

from tablename
Avatar of rafaelrgl

ASKER

Guys, i found this @@TOTAL_READ  so if we do it:

select t1.name, @@TOTAL_READ as getnumbersofrows from tb_users where ......


it will work for me
I've requested that this question be closed as follows:

Accepted answer: 0 points for rafaelrgl's comment #a39190893

for the following reason:

the answer for the question was not provide
@@TOTAL_READ:  The number of disk reads, not cache reads, by SQL Server since SQL Server was last started.
http://msdn.microsoft.com/en-us/library/ms187917.aspx

It is NOT the same as the number of rows returned in a SQL statement.
Not correct.
You give up too quickly -
select t1.name, t2.cnt as getnumbersofrows from
(select name from tbusers) as t1,
(select count(name) cnt
 from tbusers) as t2
;
yeah, what a rush. but, lets continue, i don't want to perform another select to get the count, is there anyway to do while selecting. i have something like this:


select top 100 t1.* from (select t1.name, getnumbersofrows from tb_users where ......) as t1 where .......

i have one query inside another, so i can't use the @@ROWCOUNT since it will give me 100 rows after i perform this query above, and i want to get the query inside the query, i don't know if i was clear with my writing.
The only other way I can see doing that is to create a user-defined function, getnumbersofrows, to get the total count.
The author has requested the question be closed by accepting a non-solution (their own).

Re-opening to allow the author to accept a valid solution or request the question be deleted.
thanks