rowcount inside view 
Author Message
 rowcount inside view
Is there a way to have a running total of the number of rows inside a view,
and reset that on the change of a group?

Thanks!



Sat, 25 Feb 2006 14:33:49 GMT
 rowcount inside view

HOW TO: Dynamically Number Rows in a Select Statement
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com


Quote:
> Is there a way to have a running total of the number of rows inside a
view,
> and reset that on the change of a group?

> Thanks!



Sat, 25 Feb 2006 14:42:25 GMT
 rowcount inside view
You can devise a logic similar to:

SELECT uniquecol, groupingcol,
       (SELECT COUNT(*)
          FROM tbl t1
         WHERE t1.groupingcol = t2.groupingcol
           AND t1.uniquecol <= t2.uniquecol ) AS "rank"
  FROM tbl t2 ;

--
- Anith
( Please reply to newsgroups only )



Sat, 25 Feb 2006 14:42:47 GMT
 rowcount inside view
Seeing as you didn't provide your structure here is a small demo

CREATE TABLE RunningGroupCounts(id int identity(1,1) Primary key, RepGroup
char(1))
GO
INSERT RunningGroupCounts(RepGroup) VALUES('A')
INSERT RunningGroupCounts(RepGroup) VALUES('A')
INSERT RunningGroupCounts(RepGroup) VALUES('A')
INSERT RunningGroupCounts(RepGroup) VALUES('A')
INSERT RunningGroupCounts(RepGroup) VALUES('A')
INSERT RunningGroupCounts(RepGroup) VALUES('B')
INSERT RunningGroupCounts(RepGroup) VALUES('B')
INSERT RunningGroupCounts(RepGroup) VALUES('B')
INSERT RunningGroupCounts(RepGroup) VALUES('C')
INSERT RunningGroupCounts(RepGroup) VALUES('C')
INSERT RunningGroupCounts(RepGroup) VALUES('D')
INSERT RunningGroupCounts(RepGroup) VALUES('D')
INSERT RunningGroupCounts(RepGroup) VALUES('D')
GO

SELECT
 (SELECT COUNT(*) FROM RunningGroupCounts RGCIn WHERE RGCIn.RepGroup =
RGCOut.RepGroup AND RGCIn.id < RGCOut.id) as RollingNo,
 RepGroup
FROM
 RunningGroupCounts RGCOut
ORDER By RepGroup

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:
> Is there a way to have a running total of the number of rows inside a
view,
> and reset that on the change of a group?

> Thanks!



Sat, 25 Feb 2006 14:48:54 GMT
 rowcount inside view
Thank you, people.  I think this will work for me.  I'll post back if I have
other questions about it.  You guys have a great day!


Quote:
> Is there a way to have a running total of the number of rows inside a
view,
> and reset that on the change of a group?

> Thanks!



Sat, 25 Feb 2006 14:52:20 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. set rowcount inside SP

2. SET ROWCOUNT inside of a user-defined function

3. !!!!!!!!! More then 16 Table/view inside a view !!!!!!!!!!!

4. SQL2000 - @@rowcount in views

5. IIF function inside a view

6. Create view inside SP

7. View chached inside function

8. Use a sp to Make a view with a sp inside

9. Executing a SP inside of a VIEW

10. Viewing data inside Full-Text catalogs

11. declaring a variable inside a view

12. What happens inside the view?


 
Powered by phpBB® Forum Software