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).

RowNumber Scope

What is the syntax for getting the inner-most scope for RowNumber ie within
a given group so that it behaves as follows:
Group1
rec1
Group2
rec1.1 expect to see Rowcount=1
rec1.2 expect to see Rowcount=2
rec2
Group2
rec2.1 expect to see Rowcount=1
rec2.2 expect to see Rowcount=2After posting this I realized that RowNumber won't give me the specific
rownumber just a total|||Mike
Rownumber SHOULD give you what you want... just add the name of the group as
the parameter ie
=rownumber(group1)
This gives the running count ( which would be the row number), and resets on
each new Group1 group...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and
it''''s
community of SQL Professionals.
"Mike Harbinger" wrote:
> After posting this I realized that RowNumber won't give me the specific
> rownumber just a total
>
>|||That's what I thought but it does not seem to be resetting even though I am
setting the group scope. The first row in the group is an addtional column
heading that I only want to see once per group instance. I was trying to use
RowNumber to test for row1 so I can toggle the visibiilty of the column
headers. Maybe there is a better way to do this?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:60CED50A-C9CB-4F65-B9A8-ECA1F529A69F@.microsoft.com...
> Mike
> Rownumber SHOULD give you what you want... just add the name of the group
> as
> the parameter ie
> =rownumber(group1)
> This gives the running count ( which would be the row number), and resets
> on
> each new Group1 group...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and
> it''''s
> community of SQL Professionals.
>
> "Mike Harbinger" wrote:
>> After posting this I realized that RowNumber won't give me the specific
>> rownumber just a total
>>

RowNumber reset at start of each grouping

I'm creating a report where I'd like to list out the lines of the report. I
deceided to use RowNumber function but it is keeping a running total when I
use it like this: RowNumber(Nothing)
I used the report wizard to create a report and used the page grouping to
seperate pages by "team". How do I find the scope to tell RowNumber to
startover for each team?
ColinYou can set the scope of RowNumber by replacing Nothing with the name of the
group where you want the reset to occur. If your group name is Team then you
would use RowNumber("Team").
"Colin" wrote:
> I'm creating a report where I'd like to list out the lines of the report. I
> deceided to use RowNumber function but it is keeping a running total when I
> use it like this: RowNumber(Nothing)
> I used the report wizard to create a report and used the page grouping to
> seperate pages by "team". How do I find the scope to tell RowNumber to
> startover for each team?
> Colin
>
>sql

RowNumber Problem in Grouped Data

I have multiple (multilevel) groupings in my RDL (based on line level data
from SQL server). While using the RowNumber("GrouName") I am getting strange
results. For example If that grouping was made out of 3 individual lines from
data set, then I am getting Row number as 3.
What I want is irrespective of the no of lines involved in such aggregation,
I want the rownumbers based on the final aggregated line and not based on the
no of detailed lines involved in such groupings.
Any help will be appreciated!
Thanks,
VisualcppI get this also. With my groupings, RowNumber behaves identically to
CountRows. I have something that looks like:
Group1
Group 2
Details
Group2 and details are initially hidden so I want to alternate row
colors for the group1 header and footer rows. RowNumber("Group1")
returns the number of rows in that group, not the number of the
group. It's identical to CountRows("Group1").
If you figure out the solution to this, please let me know!
On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
wrote:
> I have multiple (multilevel) groupings in my RDL (based on line level data
> from SQL server). While using theRowNumber("GrouName") I am getting strange
> results. For example If that grouping was made out of 3 individual lines from
> data set, then I am getting Row number as 3.
> What I want is irrespective of the no of lines involved in such aggregation,
> I want the rownumbers based on the final aggregated line and not based on the
> no of detailed lines involved in such groupings.
> Any help will be appreciated!
> Thanks,
> Visualcpp|||I don't know if this is the proper way to get around this, but the
following works correctly. If you happened to have blank or null
group headings this method would not work.
=IIF(RunningValue(Fields!name.Value,CountDistinct,nothing) mod 2,
"Transparent", "LightYellow")
On Mar 11, 10:35 am, brandon.rich...@.gmail.com wrote:
> I get this also. With my groupings,RowNumberbehaves identically to
> CountRows. I have something that looks like:
> Group1
> Group 2
> Details
> Group2 and details are initially hidden so I want to alternate row
> colors for the group1 header and footer rows. RowNumber("Group1")
> returns the number of rows in that group, not the number of the
> group. It's identical to CountRows("Group1").
> If you figure out the solution to this, please let me know!
> On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
> wrote:
>
> > I have multiple (multilevel) groupings in my RDL (based on line level data
> > from SQL server). While using theRowNumber("GrouName") I am getting strange
> > results. For example If that grouping was made out of 3 individual lines from
> > data set, then I am getting Row number as 3.
> > What I want is irrespective of the no of lines involved in such aggregation,
> > I want the rownumbers based on the final aggregated line and not based on the
> > no of detailed lines involved in such groupings.
> > Any help will be appreciated!
> > Thanks,
> > Visualcpp- Hide quoted text -
> - Show quoted text -

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
Max
Hi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:

> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>
|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
MaxHi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

RowNumber Group

I have a report the group is a people name and the detail is their personal
informations. I wuold like insert a row numer in the group, try but in the
list sometime jump a numeber. For example:
...
...
10 John Smith
11 Luis Abrams
13 Mike Logan
...
...
25 Sam Carter
27 Tom Grey
...
how have a solution for my problem?
Thank's
MauroHi,
you can try use running value
=3DRunningValue(Fields!keyfieldinthegroup.Value, CountDistinct,
"groupname")
On Apr 3, 10:22=A0pm, Mauro <Ma...@.discussions.microsoft.com> wrote:
> I have a report the group is a people name and the detail is their persona=l
> informations. I wuold like insert a row numer in the group, try but in the=
> list sometime jump a numeber. For example:
> ...
> ...
> 10 John Smith
> 11 Luis Abrams
> 13 Mike Logan
> ...
> ...
> 25 Sam Carter
> 27 Tom Grey
> ...
> how have a solution for my problem?
> Thank's
> Mauro

RowNumber counts hidden rows?

Hello i have an expression build on RowNumber and i realized it counts hidden
rows (visibility = false)... this seems very odd to me.. is the way it was
designed'let me be more specific... i have a expression to change the row color... but
this isn't working because the report is incrementing RowNumber on hidden
rows... so the coloring seems random to the person reading the report. Is
this normal' Is there a work around?
"thejez" wrote:
> Hello i have an expression build on RowNumber and i realized it counts hidden
> rows (visibility = false)... this seems very odd to me.. is the way it was
> designed'|||check the visibility property before set row color.
"thejez" <thejez@.discussions.microsoft.com> escreveu na mensagem
news:29D9F5ED-DB50-4C48-9BD0-C62F1005EB97@.microsoft.com...
> let me be more specific... i have a expression to change the row color...
> but
> this isn't working because the report is incrementing RowNumber on hidden
> rows... so the coloring seems random to the person reading the report. Is
> this normal' Is there a work around?
> "thejez" wrote:
>> Hello i have an expression build on RowNumber and i realized it counts
>> hidden
>> rows (visibility = false)... this seems very odd to me.. is the way it
>> was
>> designed'sql

RowNumber count within a group- please help!

I am new to reporting services, so forgive me if this is a simple question.
I have a simple report with 2 levels of grouping such as this:
Group 1
-Group 2
-item 1
-Item 2
I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
within Group 2. I read the help and it said to use RowNumber(Scope) where
scope is the name of the grouping. However, any time I put the name of the
group in, I get an error. How do I refer to the group? I'm using this:
=RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
RowNumber (Nothing) gives me the number for the outer group, but i want the
numbers in the inner group.
Any help you can provide is appreciated. Thanks!=RowNumber("Group 2")
Put the group name in double quotes!
Charles Kangai, MCDBA, MCT
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!|||ah-hah! Thank you :)
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!

RowNumber

I understand that if using this function with "nothing" between parentheses, then the running row total never resets.

However, I am hoping to have the row counter reset when a group value changes. I tried putting both the field and the defined group name in the parentheses both with and without quotes, but I get an error.

What is the correct syntax for accomplishing this?

This may help...

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

cheers,

Andrew

Row numbers in select statements

Is there an easy way to do get a row number in each row in a select
statement?
Something like:
select rownumber,description price from lineorder order by row number
Thanks,
TomTshad,
Yes.
See:
How to dynamically number rows in a SELECT statement.
http://support.microsoft.com/defaul...kb;en-us;186133
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||Why can't the presentation tier do this? It's the only place that HAS to
loop through each row, one by one. Now you're forcing the database to do it
too, and performance can only suffer for it.
http://www.aspfaq.com/2427
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eV$hgGZ2FHA.636@.TK2MSFTNGP10.phx.gbl...
> Tshad,
> Yes.
> See:
> How to dynamically number rows in a SELECT statement.
> http://support.microsoft.com/defaul...kb;en-us;186133
I tried that:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2
then r.ItemName end,Price,PurchaseQty,Price * PurchaseQty
order by 1
But in my 8 rows returned I got (1,1,1,1,1,1,2,2)'
Is the Joins causing me a problem?
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OUJZsHZ2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Why can't the presentation tier do this? It's the only place that HAS to
> loop through each row, one by one. Now you're forcing the database to do
> it too, and performance can only suffer for it.
Actually, it can.
But that is an extra step,as I am binding it to a datagrid.
Tom
> http://www.aspfaq.com/2427
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
My philosophy is that rules of database normalization apply only to physical
tables and not to query results.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||> My philosophy is that rules of database normalization apply only to
> physical tables and not to query results.
FWIW, my objection to doing this in the database has nothing to do with
normalization at all, but with the extra work required (whether using a
subquery, or copying all the data to a separate table first). A simple
counter at the presentation layer is the very least impact on performance,
since it has to loop through all rows and display them one by one anyway.
A|||This assumes that the result is being consumed in such a way that the
developer can add the additional computed column. It may be exported from
DTS to a table or file, executed only in Query Analyzer and pasted into
email, or bound to a datagrid.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utST6ik2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> FWIW, my objection to doing this in the database has nothing to do with
> normalization at all, but with the extra work required (whether using a
> subquery, or copying all the data to a separate table first). A simple
> counter at the presentation layer is the very least impact on performance,
> since it has to loop through all rows and display them one by one anyway.
> A
>|||> This assumes that the result is being consumed in such a way that the
> developer can add the additional computed column. It may be exported from
> DTS to a table or file, executed only in Query Analyzer and pasted into
> email, or bound to a datagrid.
Yep, that's why I asked, "why can't the presentation tier do this?" and did
not say "ONLY the presentation tier can do this!"|||But do you think that only the presentation tier *should* do this?
;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhisP5k2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> Yep, that's why I asked, "why can't the presentation tier do this?" and
> did not say "ONLY the presentation tier can do this!"
>

Row Numbers for Group

I have a table with 2 Groups and I want to number only Group2 with row
numbers. I put in a RowNumber(Nothing) function, and i'm getting something
like this:
Group 1
11 Group 2
14 Group 2
28 Group 2
Group 1
35 Group 2
etc...
What can I do to get the numbers to show up correctly?
Thanks!problem solved!
=RunningValue(Fields!fieldname.Value, CountDistinct, Nothing)
"jmann" wrote:
> I have a table with 2 Groups and I want to number only Group2 with row
> numbers. I put in a RowNumber(Nothing) function, and i'm getting something
> like this:
> Group 1
> 11 Group 2
> 14 Group 2
> 28 Group 2
> Group 1
> 35 Group 2
> etc...
> What can I do to get the numbers to show up correctly?
> Thanks!sql

row number

update [Costing].[dbo].[Supplier]

set n =

(

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

where t.suppliercode = Supplier.suppliercode

)

hi i cannot update a column with the row number,

it is all taking 1001, supposed to be 1001,1002 and so on .

thanks.

Hi,

Use better something like this here:

update [Costing].[dbo].[Supplier]

set n = Subquery.RowNumber

FROM [Costing].[dbo].[Supplier] S

INNER JOIN (

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

) Subquery

ON Subquery.suppliercode = S.suppliercode

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks it works , i did it shortcut way , not a good solution though.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER procedure [dbo].[DebugCursor]

as

BEGIN

SET NOCOUNT ON;

DECLARE cursorDebug Cursor FOR Select suppliercode

From supplier

Declare @.a nvarchar(25)

declare @.c int

set @.c = 300

Open cursorDebug Fetch NEXT FROM cursorDebug INTO @.a

While @.@.FETCH_STATUS =0

BEGIN

Update supplier Set n = @.c

Where suppliercode = @.a

set @.c = @.c + 1

Fetch NEXT FROM cursorDebug INTO @.a

END

CLOSE cursorDebug

DEALLOCATE cursorDebug

END

|||YOu should always prefer set based operations.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.desql