Row number in table grouped in list?

Question

Post reply

Row number in table grouped in list?

templar817

SSC Veteran

Points: 272
More actions
October 27, 2011 at 9:58 pm

#246956

Hi All, I have a dataset by market, by clients followed by some numbers. I have a list grouped by market, then a table in the list grouped by clients. This way it'll generate one table for each market, and each table is grouped by clients.
What i want to do is have a row number on each table starting from 1. If i use rownumber(nothing) or rownumber(dataset), the row number corresponds with the row number of dataset and not the table e.g.
AUSTRALIA
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
CHINA
5 xxx xxx
6 xxx xxx
7 xxx xxx
8 xxx xxx
while what i want is
AUSTRALIA
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
CHINA
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
Can this be done without me having to create one dataset for each market?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Skandalis Hall of Fame Points: 3517 More actions · Answer 1

Will this work?

ROW_NUMBER() OVER(PARTITION BY <Market> ORDER BY <sorting column>) AS 'Row Number'

templar817 SSC Veteran Points: 272 More actions · Answer 2

Hi Lian,

Forgot to mention that my database is sybase and as far as i know i can't do row number within the query using partition. I meant putting the Rownumber() function provided by SSRS into the tablecell

Skandalis Hall of Fame Points: 3517 More actions · Answer 3

Sybase also has window functions. See below:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/BCGEEBHA.htm

Or are you trying to add the row numbers in SSRS?

templar817 SSC Veteran Points: 272 More actions · Answer 4

indeed Sybase has window functions however i believe my version is older and still does not support window functions (i get Incorrect syntax near keyword 'OVER')

select ROW_NUMBER() OVER (PARTITION BY Market ORDER BY ClientName) from xxx

Yes i'm trying to add Row Number in SSRS

Skandalis Hall of Fame Points: 3517 More actions · Answer 5

Sorry, the only other thing I can think of is using RANK() instead of ROW_NUMBER().

templar817 SSC Veteran Points: 272 More actions · Answer 6

thanks. Is there no way to do this in SSRS? Seems like an obvious issue if you can't use this with a list

Row number in table grouped in list?

Cookies on SQLServerCentral