Rows Affected By Delete

Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?

Any help would be appreciated!
Brian@.@.ROWCOUNT stores the number of rows affected by the last statement. It is continually changing, so you may need to store the value in another variable immediately after your statement is executed.

rows

how do i find number of rows ?

i search almost all through google but i did not find any answer to my question

1) number of rows per page

2) total number of rows

?

thanks a lot in advance

nobody |||

Number of rows in a dataset: you could use for instance the Count() aggregate function. See MSDN for more details: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp

Number of rows on a page - you can use aggregate functions in the page header or footer that will count how often a certain textbox (e.g. located in list/table details) occurs on the current page. E.g. =Count(ReportItems!textbox1.Value)
See also: http://msdn2.microsoft.com/en-us/library/ms159677.aspx

-- Robert

rownumbering w tsql lost to ADO loop -

Hello
Thanks to all who have been helping me to fight a battel
with tsql for rownumbering of my tables. I humbly admit
defeat for today. I had to throw the towel in and whipped
up an ADO loop to number some rows in a table. That took
me all of 3 minutes, if that. I wrestled with tsql all
day long. Well, here is one more shot at tsql.
create table t (rownum int, area char(1), yearnum int,
monthnum int, areaNum int, productNum int)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 3)
this yields
rownum area yearnum monum areanum prodnum
NULL A 2003 1 1 1
NULL A 2003 1 1 2
NULL A 2003 1 1 3
NULL B 2003 1 2 1
NULL B 2003 1 2 2
NULL B 2003 1 2 3
NULL C 2003 1 3 1
NULL C 2003 1 3 2
NULL C 2003 1 3 3
NULL A 2003 2 1 1
NULL A 2003 2 1 2
NULL A 2003 2 1 3
NULL B 2003 2 2 1
NULL B 2003 2 2 2
NULL B 2003 2 2 3
NULL C 2003 2 3 1
NULL C 2003 2 3 2
NULL C 2003 2 3 3
I need to number these rows in the order of
yearNum, MonthNum, areaNum, productNum
select * from t order by yearnum, monthnum, areanum,
productnum
how can I number the rownum column starting at 1 using
tsql?
Thanks in advance,
RonDo:
UPDATE t
SET rownum = ( SELECT COUNT(*)
FROM t t1
WHERE CAST( t1.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t1.monthnum AS VARCHAR), 2 ) +
CAST( t1.areaNum AS VARCHAR) +
CAST( t1.productNum AS VARCHAR) <=
CAST( t.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t.monthnum AS VARCHAR), 2 ) +
CAST( t.areaNum AS VARCHAR) +
CAST( t.productNum AS VARCHAR) )
WHERE rownum IS NULL ;
Btw, do you really need a reason to have such a column in the table.
Generally, it is a good idea to do such derivations while extracting the
data from the table or to use a view with the ranking expression.
Anith|||thanks for your reply. This worked perfectly.
And to answer your question if I really need this
column? Truthfully, no. I don't really need it. Well,
for my purposes, this column facilitates reporting and ad-
hoc queries for data analysts in my dept at the
workplace. My real problem was that I did not know how
to achieve this with t-sql. I was aware of using the
self join, but I did not know that you had to cast int
values as varchars to accomplish this numerating.
I believe that my exercise for today will be very
valuable in the future.
Thanks for your help,
Ron

>--Original Message--
>Do:
>UPDATE t
> SET rownum = ( SELECT COUNT(*)
> FROM t t1
> WHERE CAST( t1.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t1.monthnum
AS VARCHAR), 2 ) +
> CAST( t1.areaNum AS VARCHAR) +
> CAST( t1.productNum AS VARCHAR)
<=
> CAST( t.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t.monthnum
AS VARCHAR), 2 ) +
> CAST( t.areaNum AS VARCHAR) +
> CAST( t.productNum AS VARCHAR) )
> WHERE rownum IS NULL ;
>Btw, do you really need a reason to have such a column
in the table.
>Generally, it is a good idea to do such derivations
while extracting the
>data from the table or to use a view with the ranking
expression.
>--
>Anith
>
>.
>|||One more question if I may. I hope this isn't too
ignorant sounding, but...
I noticed a semi colon at the end of your code. Does
Enterprise Manager have a wizard that can generate the t-
sql for something like this? Man, I sure hope so. It
would sure save me a lot of heartache.

>AS VARCHAR), 2 ) +
VARCHAR)
><=
>AS VARCHAR), 2 ) +
VARCHAR) )
>in the table.
>while extracting the
>expression.
>.
>|||>> Does Enterprise Manager have a wizard that can generate the t-sql for
No, they are optional. You need not worry about it.
Btw, for the problem you posted, instead of concatenating the columns you
could do a series of OR-ed correlations like:
WHERE t1.yearnum <= t.yearnum
OR ( t1.yearnum = t.yearnum
AND t1.t1.monthnum <= t.t1.monthnum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum <= t.areaNum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum = t.areaNum
AND t1.productNum <= t.productNum )
Anith|||Thanks again for this explanation. I guess I will just
have to keep experimenting. This other method did work.

>--Original Message--
generate the t-sql for
>No, they are optional. You need not worry about it.
>Btw, for the problem you posted, instead of
concatenating the columns you
>could do a series of OR-ed correlations like:
>WHERE t1.yearnum <= t.yearnum
> OR ( t1.yearnum = t.yearnum
> AND t1.t1.monthnum <= t.t1.monthnum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum <= t.areaNum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum = t.areaNum
> AND t1.productNum <= t.productNum )
>--
>Anith
>
>.
>

RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

Rownumber()

I am trying to write a stored procedure to be used for custompaging and I get error with the below SP.
"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

What am I doing wrong?

CREATEPROCEDURE dbo.GetDealerSP

(
@.startRowIndexint,
@.maximumRowsint
)
As
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax
From
(
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax,ROW_NUMBER()OVER(ORDERBY [name]DESC)AS Rowbank
FROM dealerenrollment)as DealerWithRowNumbers
WHERE Rowbank> @.startRowIndexAND RowRank<=(@.startRowIndex+ @.maximumRows)
Go

Hi bhavin78,

bhavin78:

"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

...

CREATEPROCEDURE dbo.GetDealerSP

...

WHERE Rowbank> @.startRowIndexANDRowRank<=(@.startRowIndex+ @.maximumRows)
Go

Looks like it's a typo, that RowRank should be Rowbank according to the rest of your query.

Personally, I'd change the three instances of Rowbank to RowRank, as it's a little closer to describing the columns contents (actually, calling it RowNumber would be my first choice ;) ).

I hope that helps.

|||

--zip,phone,fax, ROW_NUMBER() OVER(ORDER BY [name] DESC)ASRowbank

You have used Rowbank not RowRank check it once

Rownumber with grouping

Hi everyone

I'm having a problem that I hope someone will be able to help me with. I've created a report with a table, and using =Rownumber(nothing) to give each row a number - this works fine.

Now, however, I'm trying to create a summary report. So I've introduced grouping on one of the columns, but unfortunately the Rownumber command now doesn't seem to be taking that into account, and still showing the original row numbers. So basically I'm ending up with numbers like 2, 6, 9, 15, etc.

I've tried changing the scope from nothing to the row name and to the group name, but they all do the same thing.

Does anyone know how to solve this?

Thanks,
Matt

The grouping should make the row numbers add up properly. Are you putting the group name within double quotes, like this?

=RowNumber("MyGroupName")|||

Thanks for your reply Darrell.

Yes I am using double quotes like that. Anything else gives me a syntax error when i try to run it.

Any other thoughts. I can't work out what the problem is here. Would it help if I posted a copy of my report?

|||

Hi again

I apologise if I'm sounding petulant here, but I really am at a loss. I can't find any information anywhere on the web, and I can't work out what else I could be doing wrong. If anyone has any ideas I would really appreciate hearing them.

Thanks,
Matt

|||did you ever solve this? i am having the same challenge. there is a serious lack of documentation on this.|||

Just solved this a few minutes ago. I love it when I answer my own questions.

The key is to use CountDistinct on the value you are grouping on. So use:

=RunningValue(Fields!YourGroupField.Value, CountDistinct, Nothing)

For my whole write-up, visit:

http://maxqtech.com/CS/blogs/david_leibowitz/archive/2006/08/22/3372.aspx

David Leibowitz

Business Intelligence Practice Manager

MaxQ Technologies

|||

Hey David

Thanks for your reply. I never did get it sorted out, so I appreciate your solution. Will remember it for next time :)

Matt

RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).